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

In response to A. Kretschmer :
> 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?

Simple example:

test=# create or replace function t1_or_t2(out a int, out b int) returns
setof record as $$declare c int; begin perform * from t1; if found then
return query select * from t1; else return query select * from t2; end
if; end; $$ language plpgsql;
CREATE FUNCTION
test=*# select * from t1_or_t2();
a | b
---+---
2 | 2
(1 row)

test=*# insert into t1 values (1,1);
INSERT 0 1
test=*# select * from t1_or_t2();
a | b
---+---
1 | 1
(1 row)

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