pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedView unique rowid
4+ messages / 3 participants
[nested] [flat]
* View unique rowid
@ 2005-06-01 13:53 David Klugmann <dklugmann@hotmail.com>
2005-06-01 14:53 ` Re: View unique rowid Rafa Couto <rafacouto@gmail.com>
2005-06-01 15:21 ` Re: View unique rowid Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 4+ messages in thread
From: David Klugmann @ 2005-06-01 13:53 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: View unique rowid
2005-06-01 13:53 View unique rowid David Klugmann <dklugmann@hotmail.com>
@ 2005-06-01 14:53 ` Rafa Couto <rafacouto@gmail.com>
2005-06-01 15:17 ` Re: View unique rowid David Klugmann <dklugmann@hotmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Rafa Couto @ 2005-06-01 14:53 UTC (permalink / raw)
To: David Klugmann <dklugmann@hotmail.com>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: View unique rowid
2005-06-01 13:53 View unique rowid David Klugmann <dklugmann@hotmail.com>
2005-06-01 14:53 ` Re: View unique rowid Rafa Couto <rafacouto@gmail.com>
@ 2005-06-01 15:17 ` David Klugmann <dklugmann@hotmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: David Klugmann @ 2005-06-01 15:17 UTC (permalink / raw)
To: rafacouto@gmail.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: View unique rowid
2005-06-01 13:53 View unique rowid David Klugmann <dklugmann@hotmail.com>
@ 2005-06-01 15:21 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 0 replies; 4+ messages in thread
From: Achilleus Mantzios @ 2005-06-01 15:21 UTC (permalink / raw)
To: David Klugmann <dklugmann@hotmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-06-01 15:21 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-01 13:53 View unique rowid David Klugmann <dklugmann@hotmail.com>
2005-06-01 14:53 ` Rafa Couto <rafacouto@gmail.com>
2005-06-01 15:17 ` David Klugmann <dklugmann@hotmail.com>
2005-06-01 15:21 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox