When I create a view in PostGIS is there any way to add a unique ID to that view? Just like the "gid" field in any other PostGIS table?
Edit: Sorry I should have included this in the original post. I am using PostGresql 9.0 and PostGIS 1.5.
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.Sign up to join this community
You should be able to use the row_number() function as a column in your view. This works for Postgres 8.4 or higher.
SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY column_to_sort_by ASC) AS ROW_NUMBER, Col1, Col2 FROM table_name ) myview_name
This should work in most databases including SQL Server, Oracle, and MySQL.
For older versions of PostgreSQL (<= 8.3), here is another solution. In this example, I use another column name
vid for "view ID".
First, create a sequence. Use
CYCLE so that it will loop in the event you reach the end of the sequence.
CREATE SEQUENCE myview_vid_seq CYCLE;
Now, create a VIEW that uses the sequence:
CREATE OR REPLACE VIEW myview AS SELECT nextval('myview_vid_seq'::regclass) AS vid, mytable.* FROM mytable;