Thread: problem using regexp_replace

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

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 )

> 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

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;
BEGINtmp= '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;

> 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