Обсуждение: 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