To: pgsql-sql@postgresql.org
Path: not-for-mail
From: Jasen Betts <jasen@xnet.co.nz>
Newsgroups: gmane.comp.db.postgresql.sql
Subject: Re: problem using regexp_replace
Date: 12 Jan 2010 12:10:50 GMT
Organization: Dis (not Dat) Organisation
Lines: 35
Message-ID: <hihosa$sj4$1@reversiblemaps.ath.cx>
References: 
 <6f55ccf2a59fe7bca287389ec92e5f91.squirrel@www.webmail.fmed.uba.ar>
NNTP-Posting-Host: localhost
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
User-Agent: slrn/pre1.0.0-2 (Linux)

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;