pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFrom with case
8+ messages / 3 participants
[nested] [flat]
* From with case
@ 2013-03-25 13:48 Mauricio Cruz <cruz@sygecom.com.br>
0 siblings, 2 replies; 8+ messages in thread
From: Mauricio Cruz @ 2013-03-25 13:48 UTC (permalink / raw)
To: pgsql-sql
Hi everyone,
I'm working in a PL/SQL and I'd like to use the same
PL for 2 kinds of tables...
I have "valepag" and "valerec" both tables
have the same columns, but one is for debit and the other one is for
credit, the PL will work for both cases
with the unique diference for
the name of the table...
So I thought to use something like this:
...
For rSql in select a.adiant,
a.desc_per
from case
when
cTip='P'
then valapag
else valerec
end
where cod=2 Loop
...
But
it just dont work... does some one have other solution for this case ?
Thanks guys.
--
Grato,
Mauricio Cruz
Sygecom Informática [1]
51
3442-3975 / 3442-2345
Links:
------
[1] http://www.sygecom.com.br
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: From with case
@ 2013-03-25 14:01 Pavel Stehule <pavel.stehule@gmail.com>
parent: Mauricio Cruz <cruz@sygecom.com.br>
1 sibling, 1 reply; 8+ messages in thread
From: Pavel Stehule @ 2013-03-25 14:01 UTC (permalink / raw)
To: Mauricio Cruz <cruz@sygecom.com.br>; +Cc: pgsql-sql
Hello
> For rSql in select a.adiant,
> a.desc_per
> from case
> when cTip='P'
> then valapag
> else valerec
> end
> where cod=2 Loop
you can use a dynamic SQL, but it is not best solution usually. In
this case I usually prefer
IF cTip = 'P' THEN
FOR r IN SELECT .. FROM valapag LOOP
PERFORM proc(r);
END LOOP;
ELSE
FOR r IN SELECT .. FROM valerec LOOP
PERFORM proc(r);
END LOOP;
END IF;
with dynamic SQL
FOR r IN EXECUTE format('SELECT .. FROM %I ..', CASE WHEN ctip = 'P'
THEN 'valapag' ELSE 'valerec' END)
LOOP
..
END LOOP;
Regards
Pavel Stehule
2013/3/25 Mauricio Cruz <cruz@sygecom.com.br>:
> Hi everyone,
>
>
>
> I'm working in a PL/SQL and I'd like to use the same PL for 2 kinds of
> tables...
>
> I have "valepag" and "valerec" both tables have the same columns, but one is
> for debit and the other one is for credit, the PL will work for both cases
>
> with the unique diference for the name of the table...
>
>
>
> So I thought to use something like this:
>
> ...
>
> For rSql in select a.adiant,
> a.desc_per
> from case
> when cTip='P'
> then valapag
> else valerec
> end
> where cod=2 Loop
>
>
>
> ...
>
>
> But it just dont work... does some one have other solution for this case ?
>
>
>
> Thanks guys.
>
>
>
> --
> Grato,
> Mauricio Cruz
> Sygecom Informática
> 51 3442-3975 / 3442-2345
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: From with case
@ 2013-03-25 14:38 Mauricio Cruz <cruz@sygecom.com.br>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Mauricio Cruz @ 2013-03-25 14:38 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-sql
Thanks very much Pavel, the dynamic SQL ideia has work perfect in my
case.
Thanks so much.
--
Grato,
Mauricio Cruz
Sygecom Informática
51 3442-3975 / 3442-2345
On Mon, 25 Mar 2013 15:01:09 +0100, Pavel Stehule
<pavel.stehule@gmail.com> wrote:
> Hello
>
>
>> For rSql in select a.adiant,
>> a.desc_per
>> from case
>> when cTip='P'
>> then valapag
>> else valerec
>> end
>> where cod=2 Loop
>
>
> you can use a dynamic SQL, but it is not best solution usually. In
> this case I usually prefer
>
> IF cTip = 'P' THEN
> FOR r IN SELECT .. FROM valapag LOOP
> PERFORM proc(r);
> END LOOP;
> ELSE
> FOR r IN SELECT .. FROM valerec LOOP
> PERFORM proc(r);
> END LOOP;
> END IF;
>
> with dynamic SQL
>
> FOR r IN EXECUTE format('SELECT .. FROM %I ..', CASE WHEN ctip = 'P'
> THEN 'valapag' ELSE 'valerec' END)
> LOOP
> ..
> END LOOP;
>
> Regards
>
> Pavel Stehule
>
>
> 2013/3/25 Mauricio Cruz <cruz@sygecom.com.br>:
>> Hi everyone,
>>
>>
>>
>> I'm working in a PL/SQL and I'd like to use the same PL for 2 kinds of
>> tables...
>>
>> I have "valepag" and "valerec" both tables have the same columns, but one is
>> for debit and the other one is for credit, the PL will work for both cases
>>
>> with the unique diference for the name of the table...
>>
>>
>>
>> So I thought to use something like this:
>>
>> ...
>>
>> For rSql in select a.adiant,
>> a.desc_per
>> from case
>> when cTip='P'
>> then valapag
>> else valerec
>> end
>> where cod=2 Loop
>>
>>
>>
>> ...
>>
>>
>> But it just dont work... does some one have other solution for this case ?
>>
>>
>>
>> Thanks guys.
>>
>>
>>
>> --
>> Grato,
>> Mauricio Cruz
>> Sygecom Informática
>> 51 3442-3975 / 3442-2345
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: From with case
@ 2013-03-25 22:50 Ben Morrow <ben@morrow.me.uk>
parent: Mauricio Cruz <cruz@sygecom.com.br>
1 sibling, 2 replies; 8+ messages in thread
From: Ben Morrow @ 2013-03-25 22:50 UTC (permalink / raw)
To: cruz@sygecom.com.br; pgsql-sql
Quoth cruz@sygecom.com.br (Mauricio Cruz):
>
> I'm working in a PL/SQL and I'd like to use the same
> PL for 2 kinds of tables...
>
> I have "valepag" and "valerec" both tables
> have the same columns, but one is for debit and the other one is for
> credit, the PL will work for both cases
>
> with the unique diference for
> the name of the table...
>
> So I thought to use something like this:
> ...
>
> For rSql in select a.adiant,
> a.desc_per
> from case
> when
> cTip='P'
> then valapag
> else valerec
> end
> where cod=2 Loop
>
> ...
>
> But
> it just dont work... does some one have other solution for this case ?
I would use a view for this:
create view vale_any as
select 'P'::text "type", v.adiant, v.desc_per, v.cod
from valepag v
union all
select 'R', v.adiant, v.desc_per, v.cod
from valerec v;
then
for rSql in
select a.adiant, a.desc_per
from vale_any a
where a.type = cTip and a.cod = 2
loop
You need to cast the constant in the view definition, otherwise Pg
complains about its type being ambiguous. You should use the same type
as cTip will be.
Ben
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: From with case
@ 2013-03-26 05:33 Pavel Stehule <pavel.stehule@gmail.com>
parent: Ben Morrow <ben@morrow.me.uk>
1 sibling, 0 replies; 8+ messages in thread
From: Pavel Stehule @ 2013-03-26 05:33 UTC (permalink / raw)
To: Ben Morrow <ben@morrow.me.uk>; +Cc: pgsql-sql; Mauricio Cruz <cruz@sygecom.com.br>
This design has a performance problem. You read both tables everywhere -
for large tables can be bad
Dne 25.3.2013 23:51 "Ben Morrow" <ben@morrow.me.uk> napsal(a):
>
> Quoth cruz@sygecom.com.br (Mauricio Cruz):
> >
> > I'm working in a PL/SQL and I'd like to use the same
> > PL for 2 kinds of tables...
> >
> > I have "valepag" and "valerec" both tables
> > have the same columns, but one is for debit and the other one is for
> > credit, the PL will work for both cases
> >
> > with the unique diference for
> > the name of the table...
> >
> > So I thought to use something like this:
> > ...
> >
> > For rSql in select a.adiant,
> > a.desc_per
> > from case
> > when
> > cTip='P'
> > then valapag
> > else valerec
> > end
> > where cod=2 Loop
> >
> > ...
> >
> > But
> > it just dont work... does some one have other solution for this case ?
>
> I would use a view for this:
>
> create view vale_any as
> select 'P'::text "type", v.adiant, v.desc_per, v.cod
> from valepag v
> union all
> select 'R', v.adiant, v.desc_per, v.cod
> from valerec v;
>
> then
>
> for rSql in
> select a.adiant, a.desc_per
> from vale_any a
> where a.type = cTip and a.cod = 2
> loop
>
> You need to cast the constant in the view definition, otherwise Pg
> complains about its type being ambiguous. You should use the same type
> as cTip will be.
>
> Ben
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: From with case
@ 2013-03-26 06:22 Ben Morrow <ben@morrow.me.uk>
parent: Ben Morrow <ben@morrow.me.uk>
1 sibling, 1 reply; 8+ messages in thread
From: Ben Morrow @ 2013-03-26 06:22 UTC (permalink / raw)
To: pavel.stehule@gmail.com; pgsql-sql
Quoth pavel.stehule@gmail.com (Pavel Stehule):
> Dne 25.3.2013 23:51 "Ben Morrow" <ben@morrow.me.uk> napsal(a):
> >
> > I would use a view for this:
> >
> > create view vale_any as
> > select 'P'::text "type", v.adiant, v.desc_per, v.cod
> > from valepag v
> > union all
> > select 'R', v.adiant, v.desc_per, v.cod
> > from valerec v;
> >
> > then
> >
> > for rSql in
> > select a.adiant, a.desc_per
> > from vale_any a
> > where a.type = cTip and a.cod = 2
> > loop
>
> This design has a performance problem. You read both tables everywhere -
> for large tables can be bad
You would think so, but, in general, Pg is cleverer than that. For the
simple case of queries with constants in (so, a client-submitted query
like
select * from vale_any a where a.type = 'P' and a.cod = 2
or the equivalent with bound placeholders) the planner won't even plan
the parts of the view which don't get used. Try some experiments with
EXPLAIN to see what I mean: the unused sections of the Append (that is,
the UNION ALL) are either omitted entirely or get replaced with
Result
One-Time Filter: false
(I'm not entirely sure what makes the difference, though it seems to be
to do with how complicated the individual parts of the UNION are).
PL/pgSQL is a bit more complicated, because (unless you use EXECUTE) it
pre-plans all its statements, so the condition on a.type is not constant
at planning time. However, if you PREPARE a statement like
prepare v as select * from vale_any a
where a.type = $1 and a.cod = $2
and then run it with EXPLAIN ANALYZE EXECUTE v ('P', 2) you will see
that although the plan includes the parts of the view that don't get
used they are all marked '(never executed)' by EXPLAIN ANALYZE, because
the executor had enough information to work out they could never return
any rows. Skipping those parts of the plan at execute time does have a
small cost--for small tables you will see the total query time go up a
little for a prepared statement--but nothing like the cost of scanning a
large table. I would expect it's about the same as the cost of a
PL/pgSQL IF/THEN/ELSE.
It's worth noting at this point that if you know the rows of a UNION
will be distinct it's worth making it a UNION ALL, since otherwise Pg
has to add a sort-and-uniq step which can be expensive.
Ben
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: From with case
@ 2013-03-26 07:44 Pavel Stehule <pavel.stehule@gmail.com>
parent: Ben Morrow <ben@morrow.me.uk>
0 siblings, 1 reply; 8+ messages in thread
From: Pavel Stehule @ 2013-03-26 07:44 UTC (permalink / raw)
To: Ben Morrow <ben@morrow.me.uk>; +Cc: pgsql-sql
Hello
2013/3/26 Ben Morrow <ben@morrow.me.uk>:
> Quoth pavel.stehule@gmail.com (Pavel Stehule):
>> Dne 25.3.2013 23:51 "Ben Morrow" <ben@morrow.me.uk> napsal(a):
>> >
>> > I would use a view for this:
>> >
>> > create view vale_any as
>> > select 'P'::text "type", v.adiant, v.desc_per, v.cod
>> > from valepag v
>> > union all
>> > select 'R', v.adiant, v.desc_per, v.cod
>> > from valerec v;
>> >
>> > then
>> >
>> > for rSql in
>> > select a.adiant, a.desc_per
>> > from vale_any a
>> > where a.type = cTip and a.cod = 2
>> > loop
>>
>> This design has a performance problem. You read both tables everywhere -
>> for large tables can be bad
>
> You would think so, but, in general, Pg is cleverer than that. For the
> simple case of queries with constants in (so, a client-submitted query
> like
>
> select * from vale_any a where a.type = 'P' and a.cod = 2
>
> or the equivalent with bound placeholders) the planner won't even plan
> the parts of the view which don't get used. Try some experiments with
> EXPLAIN to see what I mean: the unused sections of the Append (that is,
> the UNION ALL) are either omitted entirely or get replaced with
>
> Result
> One-Time Filter: false
>
> (I'm not entirely sure what makes the difference, though it seems to be
> to do with how complicated the individual parts of the UNION are).
>
> PL/pgSQL is a bit more complicated, because (unless you use EXECUTE) it
> pre-plans all its statements, so the condition on a.type is not constant
> at planning time. However, if you PREPARE a statement like
>
> prepare v as select * from vale_any a
> where a.type = $1 and a.cod = $2
>
> and then run it with EXPLAIN ANALYZE EXECUTE v ('P', 2) you will see
> that although the plan includes the parts of the view that don't get
> used they are all marked '(never executed)' by EXPLAIN ANALYZE, because
> the executor had enough information to work out they could never return
> any rows. Skipping those parts of the plan at execute time does have a
> small cost--for small tables you will see the total query time go up a
> little for a prepared statement--but nothing like the cost of scanning a
> large table. I would expect it's about the same as the cost of a
> PL/pgSQL IF/THEN/ELSE.
>
> It's worth noting at this point that if you know the rows of a UNION
> will be distinct it's worth making it a UNION ALL, since otherwise Pg
> has to add a sort-and-uniq step which can be expensive.
>
> Ben
>
you have a true
CREATE OR REPLACE FUNCTION public.fo1(alfa integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
declare r record;
begin
for r in explain
select * FROM (
select 1::int as filter, * from f1
union all
select 2 as filter, * from f2) x
where x.filter = alfa
loop
raise notice '%', r;
end loop;
end;
$function$
postgres=# select fo1(1);
NOTICE: ("Append (cost=0.00..34.00 rows=2400 width=8)")
NOTICE: (" -> Seq Scan on f1 (cost=0.00..34.00 rows=2400 width=8)")
fo1
-----
(1 row)
postgres=# select fo1(2);
NOTICE: ("Append (cost=0.00..34.00 rows=2400 width=8)")
NOTICE: (" -> Seq Scan on f2 (cost=0.00..34.00 rows=2400 width=8)")
fo1
-----
(1 row)
In this case is postgres smart enough (Postgres 9.3)
Is strange, so this example doesn't work on 9.1. - PREPARE and EXECUTE
works with "one time filter", but plpgsql code doesn't work - it
returns nothing
Regards
Pavel Stehule
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: From with case
@ 2013-03-26 08:08 Pavel Stehule <pavel.stehule@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Pavel Stehule @ 2013-03-26 08:08 UTC (permalink / raw)
To: Ben Morrow <ben@morrow.me.uk>; +Cc: pgsql-sql
2013/3/26 Pavel Stehule <pavel.stehule@gmail.com>:
> Hello
>
> 2013/3/26 Ben Morrow <ben@morrow.me.uk>:
>> Quoth pavel.stehule@gmail.com (Pavel Stehule):
>>> Dne 25.3.2013 23:51 "Ben Morrow" <ben@morrow.me.uk> napsal(a):
>>> >
>>> > I would use a view for this:
>>> >
>>> > create view vale_any as
>>> > select 'P'::text "type", v.adiant, v.desc_per, v.cod
>>> > from valepag v
>>> > union all
>>> > select 'R', v.adiant, v.desc_per, v.cod
>>> > from valerec v;
>>> >
>>> > then
>>> >
>>> > for rSql in
>>> > select a.adiant, a.desc_per
>>> > from vale_any a
>>> > where a.type = cTip and a.cod = 2
>>> > loop
>>>
>>> This design has a performance problem. You read both tables everywhere -
>>> for large tables can be bad
>>
>> You would think so, but, in general, Pg is cleverer than that. For the
>> simple case of queries with constants in (so, a client-submitted query
>> like
>>
>> select * from vale_any a where a.type = 'P' and a.cod = 2
>>
>> or the equivalent with bound placeholders) the planner won't even plan
>> the parts of the view which don't get used. Try some experiments with
>> EXPLAIN to see what I mean: the unused sections of the Append (that is,
>> the UNION ALL) are either omitted entirely or get replaced with
>>
>> Result
>> One-Time Filter: false
>>
>> (I'm not entirely sure what makes the difference, though it seems to be
>> to do with how complicated the individual parts of the UNION are).
>>
>> PL/pgSQL is a bit more complicated, because (unless you use EXECUTE) it
>> pre-plans all its statements, so the condition on a.type is not constant
>> at planning time. However, if you PREPARE a statement like
>>
>> prepare v as select * from vale_any a
>> where a.type = $1 and a.cod = $2
>>
>> and then run it with EXPLAIN ANALYZE EXECUTE v ('P', 2) you will see
>> that although the plan includes the parts of the view that don't get
>> used they are all marked '(never executed)' by EXPLAIN ANALYZE, because
>> the executor had enough information to work out they could never return
>> any rows. Skipping those parts of the plan at execute time does have a
>> small cost--for small tables you will see the total query time go up a
>> little for a prepared statement--but nothing like the cost of scanning a
>> large table. I would expect it's about the same as the cost of a
>> PL/pgSQL IF/THEN/ELSE.
>>
>> It's worth noting at this point that if you know the rows of a UNION
>> will be distinct it's worth making it a UNION ALL, since otherwise Pg
>> has to add a sort-and-uniq step which can be expensive.
>>
>> Ben
>>
>
> you have a true
>
> CREATE OR REPLACE FUNCTION public.fo1(alfa integer)
> RETURNS void
> LANGUAGE plpgsql
> AS $function$
> declare r record;
> begin
> for r in explain
> select * FROM (
> select 1::int as filter, * from f1
> union all
> select 2 as filter, * from f2) x
> where x.filter = alfa
> loop
> raise notice '%', r;
> end loop;
> end;
> $function$
>
> postgres=# select fo1(1);
> NOTICE: ("Append (cost=0.00..34.00 rows=2400 width=8)")
> NOTICE: (" -> Seq Scan on f1 (cost=0.00..34.00 rows=2400 width=8)")
> fo1
> -----
>
> (1 row)
>
> postgres=# select fo1(2);
> NOTICE: ("Append (cost=0.00..34.00 rows=2400 width=8)")
> NOTICE: (" -> Seq Scan on f2 (cost=0.00..34.00 rows=2400 width=8)")
> fo1
> -----
>
> (1 row)
>
> In this case is postgres smart enough (Postgres 9.3)
>
> Is strange, so this example doesn't work on 9.1. - PREPARE and EXECUTE
> works with "one time filter", but plpgsql code doesn't work - it
> returns nothing
>
> Regards
>
> Pavel Stehule
just one note - it works on 9.1. well - my mistake - tested on
different server with different client_min_messages.
Regards
Pavel
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2013-03-26 08:08 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-03-25 13:48 From with case Mauricio Cruz <cruz@sygecom.com.br>
2013-03-25 14:01 ` Pavel Stehule <pavel.stehule@gmail.com>
2013-03-25 14:38 ` Mauricio Cruz <cruz@sygecom.com.br>
2013-03-25 22:50 ` Ben Morrow <ben@morrow.me.uk>
2013-03-26 05:33 ` Pavel Stehule <pavel.stehule@gmail.com>
2013-03-26 06:22 ` Ben Morrow <ben@morrow.me.uk>
2013-03-26 07:44 ` Pavel Stehule <pavel.stehule@gmail.com>
2013-03-26 08:08 ` Pavel Stehule <pavel.stehule@gmail.com>
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