From gherzig@fmed.uba.ar Mon Jan 11 01:55:46 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id CBF1C6333CA for ; Sun, 10 Jan 2010 21:55:46 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 58497-02 for ; Mon, 11 Jan 2010 01:55:35 +0000 (UTC) X-Greylist: delayed 00:25:10.063306 by SQLgrey-1.7.6 Received: from mail.fmed.uba.ar (mail.fmed.uba.ar [157.92.152.1]) by mail.postgresql.org (Postfix) with ESMTP id 413FE633453 for ; Sun, 10 Jan 2010 21:55:36 -0400 (AST) Received: from www.webmail.fmed.uba.ar (localhost [127.0.0.1]) by mail.fmed.uba.ar (Postfix) with ESMTP id C518F39C332 for ; Sun, 10 Jan 2010 22:29:07 -0200 (ARST) Received: from 190.55.96.62 (SquirrelMail authenticated user gherzig) by www.webmail.fmed.uba.ar with HTTP; Sun, 10 Jan 2010 22:29:07 -0200 (ARST) Message-ID: <6f55ccf2a59fe7bca287389ec92e5f91.squirrel@www.webmail.fmed.uba.ar> Date: Sun, 10 Jan 2010 22:29:07 -0200 (ARST) Subject: problem using regexp_replace From: gherzig@fmed.uba.ar To: pgsql-sql@postgresql.org User-Agent: SquirrelMail/1.4.15 MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.189 tagged_above=-10 required=5 tests=BAYES_50=0.001, FH_DATE_PAST_20XX=3.188 X-Spam-Level: *** X-Archive-Number: 201001/15 X-Sequence-Number: 33551 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;
{Action_1.842}
{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; FALSE
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 From jasen@xnet.co.nz Mon Jan 11 08:28:53 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 3E383633DB3 for ; Mon, 11 Jan 2010 04:28:53 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 24118-06 for ; Mon, 11 Jan 2010 08:28:42 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ananke.wxnz.net (ananke.wxnz.net [58.28.4.122]) by mail.postgresql.org (Postfix) with ESMTP id B4208633DAE for ; Mon, 11 Jan 2010 04:28:42 -0400 (AST) Received: from gonzo (ip-118-90-139-52.xdsl.xnet.co.nz [118.90.139.52]) by ananke.wxnz.net (Postfix) with ESMTP id 35314170CFA for ; Mon, 11 Jan 2010 21:28:39 +1300 (NZDT) Received: from news by gonzo with local (Exim 4.69) (envelope-from ) id 1NUFdd-0002JY-4f for pgsql-sql@postgresql.org; Mon, 11 Jan 2010 21:28:37 +1300 To: pgsql-sql@postgresql.org Path: not-for-mail From: Jasen Betts Newsgroups: gmane.comp.db.postgresql.sql Subject: Re: problem using regexp_replace Date: 11 Jan 2010 08:28:36 GMT Organization: Dis (not Dat) Organisation Lines: 20 Message-ID: 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 X-Trace: reversiblemaps.ath.cx 1263198516 8618 127.0.0.1 (11 Jan 2010 08:28:36 GMT) X-Face: ?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri*+R(&uv2=E1Q0Zk(>h!~o2ID@6{uf8s; a +M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W; (T"1(739R%m0Yyyv*gkGoPA.$b,D.w:z+<'"=-lVT?6 {T?=R^:W5g|E2#EhjKCa+nt":4b}dU7GYB*HBxn&Td$@f%.kl^:7X8rQWd[NTc"P"u6nkisze/Q; 8 "9Z{peQF,w)7UjV$c|RO/mQW/NMgWfr5*$-Z%u46"/00mx-,\R'fLPe.)^ User-Agent: slrn/pre1.0.0-2 (Linux) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.189 tagged_above=-10 required=5 tests=BAYES_50=0.001, FH_DATE_PAST_20XX=3.188 X-Spam-Level: *** X-Archive-Number: 201001/16 X-Sequence-Number: 33552 On 2010-01-11, 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 ) From gherzig@fmed.uba.ar Mon Jan 11 11:35:01 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id DC5976333DE for ; Mon, 11 Jan 2010 07:35:01 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 51885-01 for ; Mon, 11 Jan 2010 11:34:51 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.fmed.uba.ar (mail.fmed.uba.ar [157.92.152.1]) by mail.postgresql.org (Postfix) with ESMTP id 4FB0D6326DD for ; Mon, 11 Jan 2010 07:34:51 -0400 (AST) Received: from www.webmail.fmed.uba.ar (localhost [127.0.0.1]) by mail.fmed.uba.ar (Postfix) with ESMTP id D5FC939C5ED; Mon, 11 Jan 2010 08:33:32 -0200 (ARST) Received: from 190.55.96.62 (SquirrelMail authenticated user gherzig) by www.webmail.fmed.uba.ar with HTTP; Mon, 11 Jan 2010 08:33:33 -0200 (ARST) Message-ID: <4b9e9f07701f20f59fa63e18f73cb311.squirrel@www.webmail.fmed.uba.ar> In-Reply-To: References: <6f55ccf2a59fe7bca287389ec92e5f91.squirrel@www.webmail.fmed.uba.ar> Date: Mon, 11 Jan 2010 08:33:33 -0200 (ARST) Subject: Re: problem using regexp_replace From: gherzig@fmed.uba.ar To: "Jasen Betts" Cc: pgsql-sql@postgresql.org User-Agent: SquirrelMail/1.4.15 MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.189 tagged_above=-10 required=5 tests=BAYES_50=0.001, FH_DATE_PAST_20XX=3.188 X-Spam-Level: *** X-Archive-Number: 201001/17 X-Sequence-Number: 33553 > On 2010-01-11, 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 From jasen@xnet.co.nz Tue Jan 12 12:11:11 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id A6A85633755 for ; Tue, 12 Jan 2010 08:11:11 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 02460-07 for ; Tue, 12 Jan 2010 12:11:00 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ananke.wxnz.net (ananke.wxnz.net [58.28.4.122]) by mail.postgresql.org (Postfix) with ESMTP id 1919763344B for ; Tue, 12 Jan 2010 08:11:00 -0400 (AST) Received: from gonzo (ip-118-90-139-52.xdsl.xnet.co.nz [118.90.139.52]) by ananke.wxnz.net (Postfix) with ESMTP id 5D2D34009F for ; Wed, 13 Jan 2010 01:10:57 +1300 (NZDT) Received: from news by gonzo with local (Exim 4.69) (envelope-from ) id 1NUfaJ-0007cM-UU for pgsql-sql@postgresql.org; Wed, 13 Jan 2010 01:10:55 +1300 To: pgsql-sql@postgresql.org Path: not-for-mail From: Jasen Betts 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: 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 X-Trace: reversiblemaps.ath.cx 1263298250 29284 127.0.0.1 (12 Jan 2010 12:10:50 GMT) X-Face: ?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri*+R(&uv2=E1Q0Zk(>h!~o2ID@6{uf8s; a +M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W; (T"1(739R%m0Yyyv*gkGoPA.$b,D.w:z+<'"=-lVT?6 {T?=R^:W5g|E2#EhjKCa+nt":4b}dU7GYB*HBxn&Td$@f%.kl^:7X8rQWd[NTc"P"u6nkisze/Q; 8 "9Z{peQF,w)7UjV$c|RO/mQW/NMgWfr5*$-Z%u46"/00mx-,\R'fLPe.)^ User-Agent: slrn/pre1.0.0-2 (Linux) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.189 tagged_above=-10 required=5 tests=BAYES_50=0.001, FH_DATE_PAST_20XX=3.188 X-Spam-Level: *** X-Archive-Number: 201001/23 X-Sequence-Number: 33559 On 2010-01-11, 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; From gherzig@fmed.uba.ar Tue Jan 12 17:19:10 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 98FD2633A6B for ; Tue, 12 Jan 2010 13:19:10 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 50838-01 for ; Tue, 12 Jan 2010 17:19:00 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.fmed.uba.ar (mail.fmed.uba.ar [157.92.152.1]) by mail.postgresql.org (Postfix) with ESMTP id 03D61633816 for ; Tue, 12 Jan 2010 13:19:00 -0400 (AST) Received: from www.webmail.fmed.uba.ar (localhost [127.0.0.1]) by mail.fmed.uba.ar (Postfix) with ESMTP id 011AF39C52F; Tue, 12 Jan 2010 14:17:39 -0200 (ARST) Received: from 190.17.234.54 (SquirrelMail authenticated user gherzig) by www.webmail.fmed.uba.ar with HTTP; Tue, 12 Jan 2010 14:17:40 -0200 (ARST) Message-ID: <0862feba5dd70bb08d69d1cd4c33eb29.squirrel@www.webmail.fmed.uba.ar> In-Reply-To: References: <6f55ccf2a59fe7bca287389ec92e5f91.squirrel@www.webmail.fmed.uba.ar> Date: Tue, 12 Jan 2010 14:17:40 -0200 (ARST) Subject: Re: problem using regexp_replace From: gherzig@fmed.uba.ar To: "Jasen Betts" Cc: pgsql-sql@postgresql.org User-Agent: SquirrelMail/1.4.15 MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.189 tagged_above=-10 required=5 tests=BAYES_50=0.001, FH_DATE_PAST_20XX=3.188 X-Spam-Level: *** X-Archive-Number: 201001/24 X-Sequence-Number: 33560 > On 2010-01-11, 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
FALSE