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=HtMz6EjFdZWO14kLGuqJvpBB6EKGUOWC13zskKOrH86FSpfPlVd3Ndz+s4I6RpS6FbGlu+s+YTIjW37Zs05TJsgDQsHDLknIPSvI8znVFvRVlD6a1vU+rPwevbUHaIq62HEVUZtdNKP2eECH8D0124Tibm+rc2nSO8Os7+5h5pw=
Message-ID: <162867790707120009i7b63994l8df60afd6b9a66f9@mail.gmail.com>
Date: Thu, 12 Jul 2007 09:09:04 +0200
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: "Ashish Karalkar" <ashish.karalkar@info-spectrum.com>
Subject: Re: Using case or if to return multiple rows
Cc: pgsql-sql@postgresql.org
In-Reply-To: <005401c7c450$3d6670e0$170211ac@LIONKING.COM>
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>

Hello

what is relation between t1 and t3 and t2 and t3? Which row from t3
specifies  value?  You cannot do it in plain SQL. SQL is well for set
operations. You can use plpgsql and SRF function.


-- table1 and table2 have to have same structure
CREATE OR REPLACE FUNCTION output_tab(date)
RETURNS SETOF table1 AS $$
  DECLARE t1 table1; t2 table2;
BEGIN
  IF $1 = 'xxxx' THEN
    FOR t1 IN SELECT * FROM table1 LOOP
       RETURN NEXT t1;
    END LOOP;
  ELSE
    FOR t2 IN SELECT * FROM table1 LOOP
       RETURN NEXT t2;
    END LOOP;
  END IF;
 RETURN;
END;
$$ LANGUAGE plpgsql;

and then
SELECT * FROM output_tab('xxxxx');

Regards
Pavel Stehule


2007/7/12, Ashish Karalkar <ashish.karalkar@info-spectrum.com>:
>
>
> Hello all,
>
>
> I want to select data from two diffrent table based on third tables column
> somthing like:
>
>
> 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.
>
> Is there a anyway.
>
> Thanks in Advance
>
> With egards
> Ashish....
>