Thread: Subselects, the Oracle way

Subselects, the Oracle way

From
"Svenne Krap"
Date:
Hi,

Is there any way to mimic the oracle way of subselect, especially 
constructs like 


select * from (select col1 as x, col2, col6 from t1 union select col2 
as x, col6, col2 from t2) y order by y.x

I am aware of the fact, that it is possible to accomplish through the 
use of tempoary tables, but would rather not use them. 

Thanks in advance

Svenne
--
svenne@krap.dk
http://www.krap.dk
ICQ 5434480
PGP-key http://keys.pgp.dk:11371/pks/lookup?op=get&search=0xDF484022
PGP @ http://www.pgp.com / http://www.phpi.com


Re: Subselects, the Oracle way

From
"Joe Conway"
Date:
> Is there any way to mimic the oracle way of subselect, especially
> constructs like
>
>
> select * from (select col1 as x, col2, col6 from t1 union select col2
> as x, col6, col2 from t2) y order by y.x
>

Works fine as written in version 7.1 and above. See
http://www.postgresql.org/idocs/index.php?queries.html#QUERIES-FROM

test=# select version();                             version
-------------------------------------------------------------------PostgreSQL 7.1 on i686-pc-linux-gnu, compiled by GCC
egcs-2.91.66
(1 row)

test=# select * from (select col1 as x, col2, col6 from t1 union select col2
as x, col6, col2 from t2) y order by y.x;x | col2 | col6
---+------+------1 |    1 |    12 |    2 |    23 |    3 |    34 |    4 |    4
(4 rows)

-- Joe



Re: Subselects, the Oracle way - slightly OT

From
"Svenne Krap"
Date:
> That should work fine in 7.1 ...

Ups.. forgot to tell that I was working on 7.0.3 .. upgrade is not an 
option right now.. but I guess you answered already (not possible 
in 7.0) :)

Btw. any idea when cross-database queries will be implementet 
(you know select x.col1, y.col2 from localtable x, foreigndbtable y 
where x.col1=y.col2) ?

A real needy feature, if you ask me :)

Btw. a litte side question:
having a table with quite an amount of data (all 
telephone/addresses in denmark :) which change only very rarely 
(once every 3 months).

I our first setup it is contained in a database for its own (becase 
PGSQL 7.0 - not sure about 7.1) connects rather slowly, if giant 
tables are present. 

Is there any way to speed it up, or is a split database design the 
only way to go ?

Again TIA

Svenne
--
svenne@krap.dk
http://www.krap.dk
ICQ 5434480
PGP-key http://keys.pgp.dk:11371/pks/lookup?op=get&search=0xDF484022
PGP @ http://www.pgp.com / http://www.phpi.com


Referential Integrity Question (Delete/Insert during Transaction)

From
Stef Telford
Date:
hello again everyone,I seem to have hit what i -think- may be a bug (but i am not crying
wolf jst yet ;).
I have three tables. action, client and order_details. action has a primary
key 'order', client references action (along with adding the client_id as 
part of its primary key), order_details references client (both parts of the 
primary key there). all foreign keys (order in client and order+client_id in
order_details) are set to INITIALLY DEFERRED. so far so good i hope.
Now, i have a trigger that fires on insert, so i delete from the live 
database and then insert the changes rather than doing an update. not 
great, but shouldnt be a problem.
The problem comes when i do this:mms_post=# BEGIN;BEGINmms_post=# DELETE from client WHERE order_id = 22;DELETE
1mms_post=#INSERT INTO client mms_post-#(cli_business_name,cli_chain_id,cli_business_type,cli_short_name,cl
 
i_sic,order_id,client_id,cli_agent_bank_id,cli_operating_name,creation_id,cli_
web_page,cli_tcc,creation_date)mms_post-# VALUES ('STEFS','100-000000000333',1,'FHASDLKJH HFAKSDJ 
HKALSDJ',2534,22,'100-00000000555',230,'FHASDLKJH HFAKSDJ 
HKALSDJFH','jack','fgahsk@afsdhfkja.net','R','2001-06-18 13:46:45-04');INSERT 24211 1mms_post=# COMMIT;ERROR:
<unnamed>referential integrity violation - key in client still 
 
referenced from order_details
Now. the way i understand it, shouldnt the integrity of any foreign keys
be checked at the -end- of the transaction, after all the commands have been
processed ? it seems that the DELETE is being processed and rejected, but
the foreign key would be 'okay' due to the following INSERT.  I have tried 
SET CONSTRAINTS as well with no difference :\
Does this make any sense or am i completely mad ? (more than likely)
regards,Stefs.


Re: Subselects, the Oracle way

From
Tom Lane
Date:
"Svenne Krap" <svenne@krap.dk> writes:
> Is there any way to mimic the oracle way of subselect, especially 
> constructs like 
> select * from (select col1 as x, col2, col6 from t1 union select col2 
> as x, col6, col2 from t2) y order by y.x

That should work fine in 7.1 ...
        regards, tom lane


Re: Subselects, the Oracle way

From
Peter Eisentraut
Date:
Svenne Krap writes:

> Is there any way to mimic the oracle way of subselect, especially
> constructs like
>
> select * from (select col1 as x, col2, col6 from t1 union select col2
> as x, col6, col2 from t2) y order by y.x

I don't know what's supposed to be the "Oracle" way about this, but this
particular query should work in PostgreSQL 7.1.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter



Re: Referential Integrity Question (Delete/Insert during Transaction)

From
Stephan Szabo
Date:
On Mon, 18 Jun 2001, Stef Telford wrote:
>     
>     I seem to have hit what i -think- may be a bug (but i am not crying
> wolf jst yet ;).
> 
>     I have three tables. action, client and order_details. action has a primary
> key 'order', client references action (along with adding the client_id as 
> part of its primary key), order_details references client (both parts of the 
> primary key there). all foreign keys (order in client and order+client_id in
> order_details) are set to INITIALLY DEFERRED. so far so good i hope.
> 
>     Now. the way i understand it, shouldnt the integrity of any foreign keys
> be checked at the -end- of the transaction, after all the commands have been
> processed ? it seems that the DELETE is being processed and rejected, but
> the foreign key would be 'okay' due to the following INSERT.  I have tried 
> SET CONSTRAINTS as well with no difference :\

Yes.  There's a problem where intermediate conditions are being checked in
certain sequences of deferred constraints.  It should be fixed for 7.2.