Thread: View unique rowid

Hi
Is it possible to refer to a unique row identifier on a view ?

I have the following view but in a subsequent select I need to refer to
each row's unique identifier and I know oid's are not valid for a view.

create view persontransit
as
select personid, planet, name as aspectname, position as planetposition,
position+angle as transitposition
from personplanet, aspect
union
select personid, planet, name as aspectname, position as planetposition,
position-angle as transitposition
from personplanet, aspect
where name != 'OPPOSITION';

Many thanks

David

> Is it possible to refer to a unique row identifier on a view ?
>
> I have the following view but in a subsequent select I need to refer to
> each row's unique identifier and I know oid's are not valid for a view.


You can get an oid from some table in view definition. May be it
results to resolve your problem...

You can also try "create temp sequence" (review
http://archives.postgresql.org/pgsql-sql/2005-05/msg00126.php)


select 'saludos';

--
Rafa Couto (caligari)
mailto:rafacouto@gmail.com

Many thanks

I think the problem with using the oid from the primary table is that may 
rows in the view definition will get the same oid because of the union.

Thanks very much anyway.

I will look at the temporary sequence although I am not sure if sequences 
work with views.

Regards

David

>From: Rafa Couto <rafacouto@gmail.com>
>Reply-To: Rafa Couto <rafacouto@gmail.com>
>To: David Klugmann <dklugmann@hotmail.com>
>CC: pgsql-sql@postgresql.org
>Subject: Re: [SQL] View unique rowid
>Date: Wed, 1 Jun 2005 16:53:19 +0200
>
> > Is it possible to refer to a unique row identifier on a view ?
> >
> > I have the following view but in a subsequent select I need to refer to
> > each row's unique identifier and I know oid's are not valid for a view.
>
>
>You can get an oid from some table in view definition. May be it
>results to resolve your problem...
>
>You can also try "create temp sequence" (review
>http://archives.postgresql.org/pgsql-sql/2005-05/msg00126.php)
>
>
>select 'saludos';
>
>--
>Rafa Couto (caligari)
>mailto:rafacouto@gmail.com
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster

O David Klugmann έγραψε στις Jun 1, 2005 :

> 
> Hi
> Is it possible to refer to a unique row identifier on a view ?
> 
> I have the following view but in a subsequent select I need to refer to
> each row's unique identifier and I know oid's are not valid for a view.

Provided your ids are 4 bytes long, you could try to build an artificial 
id as

> 
> create view persontransit
> as
> select personid, planet, name as aspectname, position as planetposition,

SELECT personid::int8 as viewid,....

> position+angle as transitposition
> from personplanet, aspect
> union
> select personid, planet, name as aspectname, position as planetposition,

SELECT X'1'::int8<<32 | personid::int8 as viewid...

> position-angle as transitposition
> from personplanet, aspect
> where name != 'OPPOSITION';
> 
> Many thanks
> 
> David
>

that way the viewid is unique, + you know which part of the view
it represents by masking on the 33th bit (4294967296),
while you can get the actual personid by masking with
X'FFFFFFFF' (4294967295)
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
> 

-- 
-Achilleus