Skip site navigation (1) Skip section navigation (2)

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

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Select from second table only if select from first returns no results
Date: 2009-07-28 11:38:49
Message-ID: 20090728113849.GG1868@a-kretschmer.de (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Peter JacksonDate: 2009-07-28 12:21:39
Subject: Re: Location of databases
Previous:From: A. KretschmerDate: 2009-07-28 11:30:29
Subject: Re: Select from second table only if select from first returns no results

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group