 | |
query based on result of another query
comp.databases.postgresql comp.databases.postgresql
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
query based on result of another query -
01-13-2009
, 12:58 PM
#2
| |||
| |||
|
|
I'm accessing postgres from php and trying to avoid to much queries to db. Corently I do one select, then check with php if result is not empty, if so, then fire another select query. Is it possible to write 2 selects, so that second uses result of first? Can I check, if result of first select is empty directly on DB? something like @var = select id where id < 10 /* will be not found */ if(!@var) select id where id > 10 /*found*/ |
To the best of my knowledge, there is no way to make SQL do what you want.
What can be done is to write one query that contains both queries, like in
SELECT id FROM tab WHERE id < 10
UNION
SELECT id FROM tab WHERE id > 10
AND NOT EXISTS (SELECT 1 FROM tab WHERE id < 10)
but this will cause unnecessary work: there will be at least two and
maybe three scans of the table instead of one or two.
Besides, the query becomes much more complicated.
I'd say that this is acceptable for small and simple queries where the
cost of an additional client-server round trip hurts more than the above,
but if the query is complicated or the tables involved are big, I think
it would be a bad idea.
Yours,
Laurenz Albe
#3
| |||
| |||
|
|
Michael <useit... (AT) gmail (DOT) com> wrote: I'm accessing postgres from php and trying to avoid to much queries to db. Corently I do one select, then check with php if result is not empty, if so, then fire another select query. Is it possible to write 2 selects, so that second uses result of first? Can I check, if result of first select is empty directly on DB? something like @var = select id where id < 10 /* will be not found */ if(!@var) select id where id > 10 /*found*/ SQL describes only what you want to get, it is not a procedural language. To the best of my knowledge, there is no way to make SQL do what you want.. What can be done is to write one query that contains both queries, like in SELECT id FROM tab WHERE id < 10 UNION SELECT id FROM tab WHERE id > 10 * *AND NOT EXISTS (SELECT 1 FROM tab WHERE id < 10) but this will cause unnecessary work: there will be at least two and maybe three scans of the table instead of one or two. Besides, the query becomes much more complicated. I'd say that this is acceptable for small and simple queries where the cost of an additional client-server round trip hurts more than the above, but if the query is complicated or the tables involved are big, I think it would be a bad idea. Yours, Laurenz Albe |
thank you for reply. I thought DB could do anything for me
Regards, Michael
#4
| |||
| |||
|
|
I'm accessing postgres from php and trying to avoid to much queries to db. Corently I do one select, then check with php if result is not empty, if so, then fire another select query. Is it possible to write 2 selects, so that second uses result of first? Can I check, if result of first select is empty directly on DB? something like @var = select id where id < 10 /* will be not found */ if(!@var) select id where id > 10 /*found*/ |
running inside postgresql.
The simplest way is to use PL/pgSQL; see
http://www.postgresql.org/docs/8.3/s...-overview.html
-M-
#5
| |||
| |||
|
|
I'm accessing postgres from php and trying to avoid to much queries to db. Corently I do one select, then check with php if result is not empty, if so, then fire another select query. Is it possible to write 2 selects, so that second uses result of first? Can I check, if result of first select is empty directly on DB? something like @var = select id where id < 10 /* will be not found */ if(!@var) select id where id > 10 /*found*/ Yes, you can do this using a a function in a procedural language running inside postgresql. The simplest way is to use PL/pgSQL; see http://www.postgresql.org/docs/8.3/s...-overview.html |
I was thinking SQL only.
Yours,
Laurenz Albe
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.