problem using regexp_replace

From: gherzig(at)fmed(dot)uba(dot)ar
To: pgsql-sql(at)postgresql(dot)org
Subject: problem using regexp_replace
Date: 2010-01-11 00:29:07
Message-ID: 6f55ccf2a59fe7bca287389ec92e5f91.squirrel@www.webmail.fmed.uba.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jasen Betts 2010-01-11 08:28:36 Re: problem using regexp_replace
Previous Message msi77 2010-01-09 22:52:00 Re: how to do this query