DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=XY4/RTXfuH9Yv4IazFSfNvV6TqBZoxt8+1NCcslgvQR5lbuELI/oexifn3Cpmlpzk6uMcMJMGxooJCRwRFdAWRBQlotug9i9rRTdl+peHbZr/kRvcRjmEDctutYygfAKiUdEgb8vZs/gUOFYSDdBJ5TEpI5S80BgSqatSV6MGaY=
Message-ID: <162867790707120425p42e6de45k1342107418c80498@mail.gmail.com>
Date: Thu, 12 Jul 2007 13:25:55 +0200
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: Ragnar <gnari@hive.is>
Subject: Re: Using case or if to return multiple rows
Cc: "Ashish Karalkar" <ashish.karalkar@info-spectrum.com>,
	pgsql-sql@postgresql.org
In-Reply-To: <1184233928.5778.117.camel@localhost.localdomain>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <005401c7c450$3d6670e0$170211ac@LIONKING.COM>
	<1184233928.5778.117.camel@localhost.localdomain>

> >
> >
> > select case when  t3.date='xxxx' then
> >                     select * from table1
> > else
> >                     select * from table 2
> >  from table3  t3             where t3.date='xxxxx'
> >
> > Problem is that I have to do it in Plain SQL.
>
> you problem is not quite clear.
> do you want to output all rows from table1 (or table2)
> for each row of table3?
> or does table2 only have one row with date='xxxxx' ?
> is 'xxxx' the same date as 'xxxxx' in your example?
> if so, how can table2's columns be selected.
>
> are you looking for something like:
>
> select * from table1
>   where (select date from table3)='xxxx'
> UNION ALL
> select * from table2
>   where (select date from table3)<>'xxxx'
>

maybe little bit more readable form:

select *
   from table1
  where exists (select 1 from table3 where date = 'xxxx')
union all
select *
   from table2
  where exists (select 1 from table3 where date = 'xxxx');

it's possible with one big disadvantage. This query will do seq scan
both tables and it can be slow on big tables.

Regards
Pavel Stehule