From ashish.karalkar@info-spectrum.com Thu Jul  12 06:45:41 2007
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 12F789FB83B
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 12 Jul 2007 03:45:41 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 14064-06-5 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 12 Jul 2007 03:45:35 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from pro33.abac.com (pro33.abac.com [66.226.64.34])
	by postgresql.org (Postfix) with ESMTP id DB2D29FB6C8
	for <pgsql-sql@postgresql.org>; Thu, 12 Jul 2007 03:45:37 -0300 (ADT)
Received: from ashishk (ppp92-7.vsnl.eth.net [61.17.93.7] (may be forged))
	(authenticated bits=0)
	by pro33.abac.com (8.13.8/8.13.8) with ESMTP id l6C6jPTD033502
	(version=TLSv1/SSLv3 cipher=RC4-MD5 bits=128 verify=NO)
	for <pgsql-sql@postgresql.org>; Wed, 11 Jul 2007 23:45:30 -0700 (PDT)
	(envelope-from ashish.karalkar@info-spectrum.com)
Message-ID: <005401c7c450$3d6670e0$170211ac@LIONKING.COM>
From: "Ashish Karalkar" <ashish.karalkar@info-spectrum.com>
To: <pgsql-sql@postgresql.org>
Subject: Using case or if to return multiple rows
Date: Thu, 12 Jul 2007 12:15:24 +0530
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_004D_01C7C47E.53188DF0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/60
X-Sequence-Number: 28795

This is a multi-part message in MIME format.

------=_NextPart_000_004D_01C7C47E.53188DF0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hello all,


I want to select data from two diffrent table based on third tables =
column
somthing like:


select case when  t3.date=3D'xxxx' then
                    select * from table1

else=20
                    select * from table 2
       =20
 from table3  t3             where t3.date=3D'xxxxx'


Problem is that I have to do it in Plain SQL.

Is there a anyway.

Thanks in Advance=20

With egards
Ashish....

------=_NextPart_000_004D_01C7C47E.53188DF0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2180" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hello all,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I want to select data from two diffrent =
table based=20
on&nbsp;third tables column</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>somthing like:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>select case&nbsp;when =
&nbsp;t3.date=3D'xxxx'=20
then</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=20
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; select * from=20
table1</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>else </FONT></DIV>
<DIV><FONT face=3DArial=20
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;=20
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; select * from table 2</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; =
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;from table3=20
&nbsp;t3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
where t3.date=3D'xxxxx'</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Problem is that I have to do it in =
Plain=20
SQL.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Is there a anyway.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Thanks in Advance </FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>With egards</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Ashish....</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_004D_01C7C47E.53188DF0--




From pavel.stehule@gmail.com Thu Jul  12 07:09:11 2007
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id E0CCA9FBCC7
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 12 Jul 2007 04:09:11 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 19707-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 12 Jul 2007 04:09:06 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from wr-out-0506.google.com (wr-out-0506.google.com
 [64.233.184.234])
	by postgresql.org (Postfix) with ESMTP id D59AF9FBAB8
	for <pgsql-sql@postgresql.org>; Thu, 12 Jul 2007 04:09:08 -0300 (ADT)
Received: by wr-out-0506.google.com with SMTP id 68so31422wri
	for <pgsql-sql@postgresql.org>; Thu, 12 Jul 2007 00:09:05 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=SUzNPcIcSNteWyzYY/1k6ad8HkspC8kcVCS8qlfBOp/l7/llJkl69HHcCnpHYDUTKjk8bT5rYjkDZdGLXiU8IYHKmgHJe45Y0jhSr5BNMCGVdxsVpoqbfp83uYvQtUOf6Ib+jP2PsCagcNO2O/5g535DK5W72g7CFs212IRzlW4=
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=
Received: by 10.142.109.16 with SMTP id h16mr23888wfc.1184224144389;
	Thu, 12 Jul 2007 00:09:04 -0700 (PDT)
Received: by 10.142.88.4 with HTTP; Thu, 12 Jul 2007 00:09:04 -0700 (PDT)
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>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/61
X-Sequence-Number: 28796

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....
>



From gnari@hive.is Thu Jul  12 09:49:44 2007
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 796E89FB51A
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 12 Jul 2007 06:49:44 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 26797-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 12 Jul 2007 06:49:34 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from mx1.ipf.is (mack.ipf.is [85.197.192.164])
	by postgresql.org (Postfix) with ESMTP id 5DFE49FB7ED
	for <pgsql-sql@postgresql.org>; Thu, 12 Jul 2007 06:49:40 -0300 (ADT)
Received: (qmail 29762 invoked by uid 1010); 12 Jul 2007 09:49:34 +0000
Received: from 88.149.106.19 by mack (envelope-from <gnari@hive.is>,
	uid 1008) with qmail-scanner-1.25-st-qms
	(clamdscan: 0.88.4/1639. spamassassin: 3.1.4. perlscan: 1.25-st-qms.
	Clear:RC:1(88.149.106.19):SA:0(-2.3/4.5):.
	Processed in 0.318842 secs); 12 Jul 2007 09:49:34 -0000
X-Antivirus-HIVE-Mail-From: gnari@hive.is via mack
X-Antivirus-HIVE: 1.25-st-qms (Clear:RC:1(88.149.106.19):SA:0(-2.3/4.5):.
	Processed in 0.318842 secs Process 29755)
Received: from dsl-149-106-19.hive.is (HELO ?192.168.1.33?) (88.149.106.19)
	by mx1.ipf.is with SMTP; 12 Jul 2007 09:49:34 +0000
Subject: Re: Using case or if to return multiple rows
From: Ragnar <gnari@hive.is>
To: Ashish Karalkar <ashish.karalkar@info-spectrum.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <005401c7c450$3d6670e0$170211ac@LIONKING.COM>
References: <005401c7c450$3d6670e0$170211ac@LIONKING.COM>
Content-Type: text/plain
Date: Thu, 12 Jul 2007 09:52:08 +0000
Message-Id: <1184233928.5778.117.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.8.1 
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/62
X-Sequence-Number: 28797

On fim, 2007-07-12 at 12:15 +0530, Ashish Karalkar wrote:
> 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.

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'

this might work if table1 and table2 have compatible schemas, and table3
only has 1 row, and table3.date is NOT NULL

if this is not what you are looking for, you need to be more clear.

gnari








From pavel.stehule@gmail.com Thu Jul  12 11:26:03 2007
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id AE7B39FB55D
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 12 Jul 2007 08:26:03 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 79675-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 12 Jul 2007 08:25:53 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from an-out-0708.google.com (an-out-0708.google.com
 [209.85.132.249])
	by postgresql.org (Postfix) with ESMTP id 746449FB490
	for <pgsql-sql@postgresql.org>; Thu, 12 Jul 2007 08:26:00 -0300 (ADT)
Received: by an-out-0708.google.com with SMTP id c36so20964ana
	for <pgsql-sql@postgresql.org>; Thu, 12 Jul 2007 04:25:56 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=eJoeJ3YamfEN5HYxvFsqHg3bxHxFFIX2d1MjCVQ6rbU3G0y41EYlWvqyuWjeiIUXeqQaCcRPTqwueJmmErQNXREFNMlkG8EJVAkbWD+oppxSCsX7eJeaXhelpa2Jem9G4ht3Eo7H5uZ0B7iu/g6InGegE6Ny3LUC3EOGGmDIEtM=
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=
Received: by 10.142.187.2 with SMTP id k2mr36264wff.1184239555561;
	Thu, 12 Jul 2007 04:25:55 -0700 (PDT)
Received: by 10.142.88.4 with HTTP; Thu, 12 Jul 2007 04:25:55 -0700 (PDT)
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>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/63
X-Sequence-Number: 28798

> >
> >
> > 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



From tgl@sss.pgh.pa.us Thu Jul  12 15:35:03 2007
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id A14169FC01D
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 12 Jul 2007 12:35:03 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 96385-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 12 Jul 2007 12:34:56 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id C892E9FC017
	for <pgsql-sql@postgresql.org>; Thu, 12 Jul 2007 12:34:59 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l6CFYiCS018506;
	Thu, 12 Jul 2007 11:34:44 -0400 (EDT)
To: "Pavel Stehule" <pavel.stehule@gmail.com>
cc: Ragnar <gnari@hive.is>,
	"Ashish Karalkar" <ashish.karalkar@info-spectrum.com>,
	pgsql-sql@postgresql.org
Subject: Re: Using case or if to return multiple rows 
In-reply-to: <162867790707120425p42e6de45k1342107418c80498@mail.gmail.com> 
References: <005401c7c450$3d6670e0$170211ac@LIONKING.COM>
	<1184233928.5778.117.camel@localhost.localdomain>
	<162867790707120425p42e6de45k1342107418c80498@mail.gmail.com>
Comments: In-reply-to "Pavel Stehule" <pavel.stehule@gmail.com>
	message dated "Thu, 12 Jul 2007 13:25:55 +0200"
Date: Thu, 12 Jul 2007 11:34:44 -0400
Message-ID: <18505.1184254484@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/65
X-Sequence-Number: 28800

"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> it's possible with one big disadvantage. This query will do seq scan
> both tables and it can be slow on big tables.

No, it should be reasonably OK, because if the added condition doesn't
involve the tables being scanned it'll be turned into a one-time filter.
As an example using the regression database:

regression=# explain select * from tenk1 where 1 in (select f1 from int4_tbl)
union all
select * from tenk1 where 1 not in (select f1 from int4_tbl);
                               QUERY PLAN
-------------------------------------------------------------------------
 Append  (cost=1.06..1118.13 rows=20000 width=244)
   ->  Result  (cost=1.06..459.06 rows=10000 width=244)
         One-Time Filter: (hashed subplan)
         ->  Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)
         SubPlan
           ->  Seq Scan on int4_tbl  (cost=0.00..1.05 rows=5 width=4)
   ->  Result  (cost=1.06..459.06 rows=10000 width=244)
         One-Time Filter: (NOT (hashed subplan))
         ->  Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)
         SubPlan
           ->  Seq Scan on int4_tbl  (cost=0.00..1.05 rows=5 width=4)
(11 rows)

The probe into int4_tbl will happen twice (but not more than that)
and whichever tenk1 scan gets discarded won't happen at all.  So
unless the test condition itself is horribly expensive this should
work as well as you could expect.

			regards, tom lane