Re: [SQL] From with case
Hello
2013/3/26 Ben Morrow :
> Quoth pavel.steh...@gmail.com (Pavel Stehule):
>> Dne 25.3.2013 23:51 "Ben Morrow" 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
Re: [SQL] From with case
2013/3/26 Pavel Stehule :
> Hello
>
> 2013/3/26 Ben Morrow :
>> Quoth pavel.steh...@gmail.com (Pavel Stehule):
>>> Dne 25.3.2013 23:51 "Ben Morrow" 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
[SQL] how can I replace all instances of a pattern
I'm trying remove all instances of non-alphanumeric or underscore characters from a query result for further use. This is part of a function I'm writing that is in plpgsql Examples: Original value 'My text1' 'My text 2' 'My-text-3' 'My_text4' 'My!text5' Desired 'Mytext1' 'Mytext2' 'Mytext3' 'My_text4' (no change) 'Mytext5' The field containing the text is column_name. I tried the following: Select regexp_replace(column_name,'\W','') from mytable This deals with the correct characters but only does the first instance of the character so the output is: 'My text1' 'Mytext 2' (wrong) 'Mytext-3' (wrong) 'My_text4' 'My!text5' I managed to get the desired output by writing the text into a variable through a loop and then just keep looping on the variable until all the characters are removed: sql_qry:= 'select column_name from mytable'; for sql_record in execute sql_qry loop curr_record := sql_record.column_name; while length(substring(curr_record from '\W'))>0 loop curr_record := regexp_replace(curr_record, '\W',''); end loop; . rest of the code This works but it seems like a lot of work to do something this simple but I cannot find any function that will replace all instances of a string AND can base it on a regular expression pattern. Is there a better way to do this in 9.1?
Re: [SQL] how can I replace all instances of a pattern
Sorry, caught a typo. Mytext1 is correctly replaced because only one instance of the character (space) is in the string. This deals with the correct characters but only does the first instance of the character so the output is: 'Mytext1' 'Mytext 2' (wrong) 'Mytext-3' (wrong) 'My_text4' 'My!text5'
Re: [SQL] how can I replace all instances of a pattern
On Tue, Mar 26, 2013 at 09:13:39AM -0400, James Sharrett wrote:
> Sorry, caught a typo. Mytext1 is correctly replaced because only one
> instance of the character (space) is in the string.
>
> This deals with the correct characters but only does the first instance of
> the character so the output is:
>
> 'Mytext1'
> 'Mytext 2' (wrong)
> 'Mytext-3' (wrong)
> 'My_text4'
> 'My!text5'
>
Hi James,
Try adding the g flag to the regex (for global). From the documentation:
regexp_replace('foobarbaz', 'b..', 'X')
fooXbaz
regexp_replace('foobarbaz', 'b..', 'X', 'g')
fooXX
regexp_replace('foobarbaz', 'b(..)', E'X\\1Y', 'g')
fooXarYXazY
Regards,
Ken
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how can I replace all instances of a pattern
On 03/26/2013 06:08 AM, James Sharrett wrote: I'm trying remove all instances of non-alphanumeric or underscore characters from a query result for further use. This is part of a function I'm writing that is in plpgsql Examples: Original value 'My text1' 'My text 2' 'My-text-3' 'My_text4' 'My!text5' Desired 'Mytext1' 'Mytext2' 'Mytext3' 'My_text4' (no change) 'Mytext5' The field containing the text is column_name. I tried the following: Select regexp_replace(column_name,'\W','') from mytable This deals with the correct characters but only does the first instance of the character so the output is: 'My text1' 'Mytext 2' (wrong) 'Mytext-3' (wrong) 'My_text4' 'My!text5' I managed to get the desired output by writing the text into a variable through a loop and then just keep looping on the variable until all the characters are removed: sql_qry:= 'select column_name from mytable'; for sql_record in execute sql_qry loop curr_record := sql_record.column_name; while length(substring(curr_record from '\W'))>0 loop curr_record := regexp_replace(curr_record, '\W',''); end loop; rest of the code This works but it seems like a lot of work to do something this simple but I cannot find any function that will replace all instances of a string AND can base it on a regular expression pattern. Is there a better way to do this in 9.1? You were on the right track with regexp_replace but you need to add a global flag: regexp_replace(column_name,'\W','','g') See examples under http://www.postgresql.org/docs/9.1/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP Cheers, Steve
Re: [SQL] how can I replace all instances of a pattern
Thanks Ken! I missed that option going through the documentation. > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
