In PostGIS is it possible to create a View with a unique ID?

Question

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.

Ando

Answer 1

You should be able to use the row_number() function as a column in your view. This works for Postgres 8.4 or higher.

http://www.postgresql.org/docs/current/static/functions-window.html

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.

Answer 2

I don't think that is possible. You could create temporary table and add unique column to that table but then you have a bad database design if your data is not static. Also you could create a function that would create combination of some ID's or other columns from all tables that are used in the view and then add this function to your view. It is not perfect but could work for you.

Answer 3

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;