pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem using regexp_replace
5+ messages / 2 participants
[nested] [flat]
* problem using regexp_replace
@ 2010-01-11 00:29 gherzig@fmed.uba.ar
0 siblings, 2 replies; 5+ messages in thread
From: gherzig@fmed.uba.ar @ 2010-01-11 00:29 UTC (permalink / raw)
To: pgsql-sql
Hi all. Im having a hard time here. Really have no idea what is wrong here.
Facing a special case of text substitution, i have to parse a column like
this one:
SELECT formato from table where id=1;
<TABLE><TBODY><TR><TD>{Action_1.842}</TD></TR></TBODY></TABLE><TABLE><TBODY><TR><TD>{Action_2.921}[truncated]
The numbers at the rigth of the period identifies an argument to the
function identified to "Action_x"
Every {Action_x....} is asociated to a diff function , so i have a helper
function to identify the "Action" part:
CREATE FUNCTION valores_sustitucion(valor_ingresado varchar[])
returns varchar
as
$$
select case
$1[1] when 'Action_1' then
(select descripcion from load_by_cod($1[2]))
when 'Action_2' then (select descripcion from pay_by_view($1[2])
else 'FALSE'
end;
$$ language sql;
So, the idea is, to call associated function with every "Action_x", with
the number as the argument to that associated function.
So, i come with this:
SELECT regexp_replace(
formato, E'{([^.]*)\.([a-zA-Z0-9]*)},
valores_sustitucion(ARRAY[E'\\1'::varchar,E'\\2'::varchar]),
'g')
from table where id =1;
<TD>FALSE</TD></TR></TBODY></TABLE><TABLE><TBODY><TR><TD>FALSE</TD>
The valores_sustitucion() functions is called, but the case construction
is failing. I have tested the regular expression, and its fine.
It looks like is something with the grouping and using that groups as the
argument of the valores_sustiticion() funcion.
Anybody has a hint?
Thanks!
Gerardo
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problem using regexp_replace
@ 2010-01-11 08:28 Jasen Betts <jasen@xnet.co.nz>
parent: gherzig@fmed.uba.ar
1 sibling, 1 reply; 5+ messages in thread
From: Jasen Betts @ 2010-01-11 08:28 UTC (permalink / raw)
To: pgsql-sql
On 2010-01-11, gherzig@fmed.uba.ar <gherzig@fmed.uba.ar> wrote:
> CREATE FUNCTION valores_sustitucion(valor_ingresado varchar[])
> returns varchar
> as
> $$
> select case
> $1[1] when 'Action_1' then
> (select descripcion from load_by_cod($1[2]))
>
> when 'Action_2' then (select descripcion from pay_by_view($1[2])
>
> else 'FALSE'
> end;
> $$ language sql;
> Anybody has a hint?
you are missing a )
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problem using regexp_replace
@ 2010-01-11 10:33 gherzig@fmed.uba.ar
parent: Jasen Betts <jasen@xnet.co.nz>
0 siblings, 0 replies; 5+ messages in thread
From: gherzig@fmed.uba.ar @ 2010-01-11 10:33 UTC (permalink / raw)
To: Jasen Betts <jasen@xnet.co.nz>; +Cc: pgsql-sql
> On 2010-01-11, gherzig@fmed.uba.ar <gherzig@fmed.uba.ar> wrote:
>
>> CREATE FUNCTION valores_sustitucion(valor_ingresado varchar[])
>> returns varchar
>> as
>> $$
>> select case
>> $1[1] when 'Action_1' then
>> (select descripcion from load_by_cod($1[2]))
>>
>> when 'Action_2' then (select descripcion from pay_by_view($1[2])
>>
>> else 'FALSE'
>> end;
>> $$ language sql;
>
>> Anybody has a hint?
>
> you are missing a )
>
Oh, thats a copy-paste problem, sory about that.
I forgot to mention, this is a 8.3 running on linux.
Gerardo
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problem using regexp_replace
@ 2010-01-12 12:10 Jasen Betts <jasen@xnet.co.nz>
parent: gherzig@fmed.uba.ar
1 sibling, 1 reply; 5+ messages in thread
From: Jasen Betts @ 2010-01-12 12:10 UTC (permalink / raw)
To: pgsql-sql
On 2010-01-11, gherzig@fmed.uba.ar <gherzig@fmed.uba.ar> wrote:
> So, i come with this:
> SELECT regexp_replace(
> formato, E'{([^.]*)\.([a-zA-Z0-9]*)},
> valores_sustitucion(ARRAY[E'\\1'::varchar,E'\\2'::varchar]),
> 'g')
> from table where id =1;
select valores_sustitucion(ARRAY[E'\\1'::varchar,E'\\2'::varchar]);
valores_sustitucion
---------------------
FALSE
that's the problem you are getting, the valores_sustitucion works on
the values given and that result is given to regexp_replace.
try this:
create OR REPLACE function magic( inp text ) returns text as $F$
DECLARE
tmp text;
res text;
BEGIN
tmp= 'SELECT ' || regexp_replace(quote_literal(inp),E'{([^.]*)\.([a-zA-Z0-9]*)}',
$s$'|| valores_sustitucion(ARRAY[E'\1',E'\2']) ||'$s$,'g');
-- raise notice 'tmp=%',(tmp);
EXECUTE tmp INTO res;
RETURN res;
END;
$F$ language plpgsql;
SELECT magic( formato ) FROM from table where id =1;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problem using regexp_replace
@ 2010-01-12 16:17 gherzig@fmed.uba.ar
parent: Jasen Betts <jasen@xnet.co.nz>
0 siblings, 0 replies; 5+ messages in thread
From: gherzig@fmed.uba.ar @ 2010-01-12 16:17 UTC (permalink / raw)
To: Jasen Betts <jasen@xnet.co.nz>; +Cc: pgsql-sql
> On 2010-01-11, gherzig@fmed.uba.ar <gherzig@fmed.uba.ar> wrote:
>
>> So, i come with this:
>> SELECT regexp_replace(
>> formato, E'{([^.]*)\.([a-zA-Z0-9]*)},
>> valores_sustitucion(ARRAY[E'\\1'::varchar,E'\\2'::varchar]),
>> 'g')
>> from table where id =1;
>
> select valores_sustitucion(ARRAY[E'\\1'::varchar,E'\\2'::varchar]);
>
> valores_sustitucion
> ---------------------
> FALSE
>
> that's the problem you are getting, the valores_sustitucion works on
> the values given and that result is given to regexp_replace.
>
> try this:
>
> create OR REPLACE function magic( inp text ) returns text as $F$
> DECLARE
> tmp text;
> res text;
> BEGIN
> tmp= 'SELECT ' ||
> regexp_replace(quote_literal(inp),E'{([^.]*)\.([a-zA-Z0-9]*)}',
> $s$'|| valores_sustitucion(ARRAY[E'\1',E'\2']) ||'$s$,'g');
> -- raise notice 'tmp=%',(tmp);
> EXECUTE tmp INTO res;
> RETURN res;
> END;
> $F$ language plpgsql;
>
> SELECT magic( formato ) FROM from table where id =1;
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
>
You hit that really hard, Jasen, thank you very much!!
You save my week :)
Thanks again.
Gerardo
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-01-12 16:17 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-11 00:29 problem using regexp_replace gherzig@fmed.uba.ar
2010-01-11 08:28 ` Jasen Betts <jasen@xnet.co.nz>
2010-01-11 10:33 ` gherzig@fmed.uba.ar
2010-01-12 12:10 ` Jasen Betts <jasen@xnet.co.nz>
2010-01-12 16:17 ` gherzig@fmed.uba.ar
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