Re: Select from second table only if select from first returns no results

In response to Petros Thespis :
> Hello all,
>
> I'm facing the following problem.
>
> I have a database with two tables, t1 and t2. The two tables have the same
> simple structure, say columns col1, col2. What I want to do is to select from
> t1 and, in case no results are returned, to then select from t2.
>
> Right now, all I'm doing is
>
> SELECT col1 FROM t1 WHERE col2 = "STH"
> UNION
> SELECT col1 FROM t2 WHERE col2 = "STH";
>
> That is, as far as I know, I always check both t1 and t2 and, moreover, I get
> no guarrantee that t1 entries will come first in the list of the results.
>
> Any ideas on how to solve this?

You can add an extra column containing the table-name:

test=*# select * from t1;
a | b
---+---
(0 rows)

test=*# select * from t2;
a | b
---+---
2 | 2
(1 row)

test=*# select 't1' as tab, * from t1 union all select 't2', * from t2;
tab | a | b
-----+---+---
t2 | 2 | 2
(1 row)

But that's not a full solution for you. I think, you can write a simple
function in plpgsql to solve your problem. Do you need more help?

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net