Re: problem using regexp_replace - Mailing list 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;
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;