pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing case or if to return multiple rows
5+ messages / 4 participants
[nested] [flat]
* Using case or if to return multiple rows
@ 2007-07-12 06:45 Ashish Karalkar <ashish.karalkar@info-spectrum.com>
2007-07-12 07:09 ` Re: Using case or if to return multiple rows Pavel Stehule <pavel.stehule@gmail.com>
2007-07-12 09:52 ` Re: Using case or if to return multiple rows Ragnar <gnari@hive.is>
0 siblings, 2 replies; 5+ messages in thread
From: Ashish Karalkar @ 2007-07-12 06:45 UTC (permalink / raw)
To: pgsql-sql
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....
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Using case or if to return multiple rows
2007-07-12 06:45 Using case or if to return multiple rows Ashish Karalkar <ashish.karalkar@info-spectrum.com>
@ 2007-07-12 07:09 ` Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Pavel Stehule @ 2007-07-12 07:09 UTC (permalink / raw)
To: Ashish Karalkar <ashish.karalkar@info-spectrum.com>; +Cc: pgsql-sql
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....
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Using case or if to return multiple rows
2007-07-12 06:45 Using case or if to return multiple rows Ashish Karalkar <ashish.karalkar@info-spectrum.com>
@ 2007-07-12 09:52 ` Ragnar <gnari@hive.is>
2007-07-12 11:25 ` Re: Using case or if to return multiple rows Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Ragnar @ 2007-07-12 09:52 UTC (permalink / raw)
To: Ashish Karalkar <ashish.karalkar@info-spectrum.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Using case or if to return multiple rows
2007-07-12 06:45 Using case or if to return multiple rows Ashish Karalkar <ashish.karalkar@info-spectrum.com>
2007-07-12 09:52 ` Re: Using case or if to return multiple rows Ragnar <gnari@hive.is>
@ 2007-07-12 11:25 ` Pavel Stehule <pavel.stehule@gmail.com>
2007-07-12 15:34 ` Re: Using case or if to return multiple rows Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Pavel Stehule @ 2007-07-12 11:25 UTC (permalink / raw)
To: Ragnar <gnari@hive.is>; +Cc: Ashish Karalkar <ashish.karalkar@info-spectrum.com>; pgsql-sql
> >
> >
> > 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Using case or if to return multiple rows
2007-07-12 06:45 Using case or if to return multiple rows Ashish Karalkar <ashish.karalkar@info-spectrum.com>
2007-07-12 09:52 ` Re: Using case or if to return multiple rows Ragnar <gnari@hive.is>
2007-07-12 11:25 ` Re: Using case or if to return multiple rows Pavel Stehule <pavel.stehule@gmail.com>
@ 2007-07-12 15:34 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2007-07-12 15:34 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Ragnar <gnari@hive.is>; Ashish Karalkar <ashish.karalkar@info-spectrum.com>; pgsql-sql
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-07-12 15:34 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-12 06:45 Using case or if to return multiple rows Ashish Karalkar <ashish.karalkar@info-spectrum.com>
2007-07-12 07:09 ` Pavel Stehule <pavel.stehule@gmail.com>
2007-07-12 09:52 ` Ragnar <gnari@hive.is>
2007-07-12 11:25 ` Pavel Stehule <pavel.stehule@gmail.com>
2007-07-12 15:34 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox