From leif@solumslekt.org Tue Apr 27 11:44:02 2010 Received: from maia.hub.org (maia-4.hub.org [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id DD8B2632971 for ; Tue, 27 Apr 2010 08:44:02 -0300 (ADT) 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 31668-10 for ; Tue, 27 Apr 2010 11:43:53 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail48.e.nsc.no (mail48.e.nsc.no [193.213.115.48]) by mail.postgresql.org (Postfix) with ESMTP id 84A6A6327FD for ; Tue, 27 Apr 2010 08:43:50 -0300 (ADT) Received: from balapapa.localnet (42.185.16.62.customer.cdi.no [62.16.185.42]) (authenticated bits=0) by mail48.nsc.no (8.14.3/8.14.3) with ESMTP id o3RBhmJw002350 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT) for ; Tue, 27 Apr 2010 13:43:48 +0200 (MEST) From: Leif Biberg Kristensen To: pgsql-sql@postgresql.org Subject: regexp_replace and search/replace values stored in table Date: Tue, 27 Apr 2010 13:43:48 +0200 User-Agent: KMail/1.12.4 (Linux/2.6.32-gentoo-r7; KDE/4.3.5; i686; ; ) MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Message-Id: <201004271343.48070.leif@solumslekt.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.8 tagged_above=-10 required=5 tests=BAYES_50=0.8 X-Spam-Level: X-Archive-Number: 201004/96 X-Sequence-Number: 34023 I've got a system for entering and storing a lot of standard hyperlinks in a compact form, and then expand them at run time like this: CREATE OR REPLACE FUNCTION _my_expand(TEXT) RETURNS TEXT AS $$ -- private func, expand various compact links DECLARE str TEXT = $1; BEGIN -- Scanned church books [kb=book reference|image reference|link text] str := REGEXP_REPLACE(str, E'\\[kb=(.+?)\\|(.+?)\\|(.+?)\\]', E'\\3', 'g'); -- Scanned probate registers [sk=protocol|image reference|link text] str := REGEXP_REPLACE(str, E'\\[sk=(.+?)\\|(.+?)\\|(.+?)\\]', E'\\3', 'g'); -- Scanned deed ("pantebok") registers [sk=protocol|image reference|link text] str := REGEXP_REPLACE(str, E'\\[tl=(.+?)\\|(.+?)\\|(.+?)\\]', E'\\3', 'g'); RETURN str; END $$ LANGUAGE plpgsql STABLE; According to the slogan "minimize code, maximize data" I feel that these strings should be stored in a table: CREATE TABLE short_links ( link_type CHAR(2) PRIMARY KEY, short_link TEXT, long_link TEXT, description TEXT ); It appears like I have to double the number of backslashes when I enter the data: INSERT INTO short_links (link_type, short_link, long_link, description) VALUES ('sk', E'\\\\[sk=(.+?)\\\\|(.+?)\\\\|(.+?)\\\\]', E'\\\\3', 'Scanned probate registers [sk=protocol|image reference|link text]'); pgslekt=> select * from short_links; link_type | short_link | long_link | description -----------+--------------------------------+-----------------------------------------------------------------------------------------------------------+------------------------------------------------------------------- sk | \\[sk=(.+?)\\|(.+?)\\|(.+?)\\] | \\3 | Scanned probate registers [sk=protocol|image reference|link text] (1 row) So far, so good. But when I try to do the actual expansion, I'm stumped. pgslekt=> select regexp_replace((select source_text from sources where source_id=23091), (select quote_literal(short_link) from short_links where link_type = 'sk'), (select quote_literal(long_link) from short_links where link_type = 'sk'), 'g'); regexp_replace ------------------------------------------------------------------------------------------------ [sk=25658|67|side 66a]. Vabakken under Klyve vestre i Solum 07.07.1784: [p=6947|Isach Jonsen]. (1 row) What am I missing? regards, -- Leif Biberg Kristensen http://solumslekt.org/ From tim@tim-landscheidt.de Tue Apr 27 13:04:54 2010 Received: from maia.hub.org (maia-4.hub.org [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 3785F6325B0 for ; Tue, 27 Apr 2010 10:04:54 -0300 (ADT) 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 46440-01 for ; Tue, 27 Apr 2010 13:04:44 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from lo.gmane.org (lo.gmane.org [80.91.229.12]) by mail.postgresql.org (Postfix) with ESMTP id 84AA46321C5 for ; Tue, 27 Apr 2010 10:04:43 -0300 (ADT) Received: from list by lo.gmane.org with local (Exim 4.69) (envelope-from ) id 1O6kSo-0007N7-P7 for pgsql-sql@postgresql.org; Tue, 27 Apr 2010 15:04:34 +0200 Received: from e177123006.adsl.alicedsl.de ([85.177.123.6]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 27 Apr 2010 15:04:34 +0200 Received: from tim by e177123006.adsl.alicedsl.de with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 27 Apr 2010 15:04:34 +0200 X-Injected-Via-Gmane: http://gmane.org/ Mail-Followup-To: pgsql-sql@postgresql.org To: pgsql-sql@postgresql.org connect(): No such file or directory From: Tim Landscheidt Subject: Re: regexp_replace and search/replace values stored in table Date: Tue, 27 Apr 2010 13:04:23 +0000 Organization: Lines: 20 Message-ID: References: <201004271343.48070.leif@solumslekt.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@dough.gmane.org X-Gmane-NNTP-Posting-Host: e177123006.adsl.alicedsl.de Mail-Copies-To: never User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux) Cancel-Lock: sha1:gpVTaT2YiRJOBOoEX2hvszD70bk= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.011 tagged_above=-10 required=5 tests=BAYES_40=-0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201004/97 X-Sequence-Number: 34024 Leif Biberg Kristensen wrote: > [...] > So far, so good. But when I try to do the actual expansion, I'm stumped. > pgslekt=> select regexp_replace((select source_text from sources where > source_id=23091), (select quote_literal(short_link) from short_links where > link_type = 'sk'), (select quote_literal(long_link) from short_links where > link_type = 'sk'), 'g'); > regexp_replace > ------------------------------------------------------------------------------------------------ > [sk=25658|67|side 66a]. Vabakken under Klyve vestre i Solum 07.07.1784: > [p=6947|Isach Jonsen]. > (1 row) > What am I missing? For starters, omit the call to quote_literal (). Tim From leif@solumslekt.org Tue Apr 27 13:10:42 2010 Received: from maia.hub.org (maia-4.hub.org [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id B15FC63279B for ; Tue, 27 Apr 2010 10:10:42 -0300 (ADT) 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 47066-09 for ; Tue, 27 Apr 2010 13:10:32 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail47.e.nsc.no (mail47.e.nsc.no [193.213.115.47]) by mail.postgresql.org (Postfix) with ESMTP id 45D196321C5 for ; Tue, 27 Apr 2010 10:10:30 -0300 (ADT) Received: from balapapa.localnet (42.185.16.62.customer.cdi.no [62.16.185.42]) (authenticated bits=0) by mail47.nsc.no (8.14.3/8.14.3) with ESMTP id o3RDARpB008319 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT) for ; Tue, 27 Apr 2010 15:10:27 +0200 (MEST) From: Leif Biberg Kristensen To: pgsql-sql@postgresql.org Subject: Re: regexp_replace and search/replace values stored in table Date: Tue, 27 Apr 2010 15:10:26 +0200 User-Agent: KMail/1.12.4 (Linux/2.6.32-gentoo-r7; KDE/4.3.5; i686; ; ) References: <201004271343.48070.leif@solumslekt.org> In-Reply-To: MIME-Version: 1.0 Content-Type: Text/Plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Message-Id: <201004271510.26539.leif@solumslekt.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.001 tagged_above=-10 required=5 tests=BAYES_40=-0.001 X-Spam-Level: X-Archive-Number: 201004/98 X-Sequence-Number: 34025 On Tuesday 27. April 2010 15.04.23 Tim Landscheidt wrote: > Leif Biberg Kristensen wrote: > > > [...] > > So far, so good. But when I try to do the actual expansion, I'm stumped. > > > pgslekt=> select regexp_replace((select source_text from sources where > > source_id=23091), (select quote_literal(short_link) from short_links where > > link_type = 'sk'), (select quote_literal(long_link) from short_links where > > link_type = 'sk'), 'g'); > > regexp_replace > > ------------------------------------------------------------------------------------------------ > > [sk=25658|67|side 66a]. Vabakken under Klyve vestre i Solum 07.07.1784: > > [p=6947|Isach Jonsen]. > > (1 row) > > > What am I missing? > > For starters, omit the call to quote_literal (). > > Tim Tim, that was actually the first thing i tried, but I omitted it from the post: pgslekt=> select regexp_replace((select source_text from sources where source_id=23091), (select short_link from short_links where link_type = 'sk'), (select long_link from short_links where link_type = 'sk'), 'g'); regexp_replace ------------------------------------------------------------------------------------------------ [sk=25658|67|side 66a]. Vabakken under Klyve vestre i Solum 07.07.1784: [p=6947|Isach Jonsen]. (1 row) It doesn't work either. regards, -- Leif Biberg Kristensen http://solumslekt.org/ From leif@solumslekt.org Tue Apr 27 13:32:38 2010 Received: from maia.hub.org (maia-4.hub.org [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 6C000632563 for ; Tue, 27 Apr 2010 10:32:38 -0300 (ADT) 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 50663-05 for ; Tue, 27 Apr 2010 13:32:29 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail48.e.nsc.no (mail48.e.nsc.no [193.213.115.48]) by mail.postgresql.org (Postfix) with ESMTP id 64276632BEC for ; Tue, 27 Apr 2010 10:32:26 -0300 (ADT) Received: from balapapa.localnet (42.185.16.62.customer.cdi.no [62.16.185.42]) (authenticated bits=0) by mail48.nsc.no (8.14.3/8.14.3) with ESMTP id o3RDWK5R021428 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT) for ; Tue, 27 Apr 2010 15:32:24 +0200 (MEST) From: Leif Biberg Kristensen To: pgsql-sql@postgresql.org Subject: Re: regexp_replace and search/replace values stored in table Date: Tue, 27 Apr 2010 15:32:20 +0200 User-Agent: KMail/1.12.4 (Linux/2.6.32-gentoo-r7; KDE/4.3.5; i686; ; ) References: <201004271343.48070.leif@solumslekt.org> In-Reply-To: <201004271343.48070.leif@solumslekt.org> MIME-Version: 1.0 Content-Type: Text/Plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Message-Id: <201004271532.20639.leif@solumslekt.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.8 tagged_above=-10 required=5 tests=BAYES_50=0.8 X-Spam-Level: X-Archive-Number: 201004/99 X-Sequence-Number: 34026 On Tuesday 27. April 2010 13.43.48 Leif Biberg Kristensen wrote: > CREATE TABLE short_links ( > link_type CHAR(2) PRIMARY KEY, > short_link TEXT, > long_link TEXT, > description TEXT > ); > > It appears like I have to double the number of backslashes when I enter the > data: > > INSERT INTO short_links (link_type, short_link, long_link, description) VALUES > ('sk', E'\\\\[sk=(.+?)\\\\|(.+?)\\\\|(.+?)\\\\]', > E'\\\\3', > 'Scanned probate registers [sk=protocol|image reference|link text]'); I found the solution. I was totally wrong about the 4 backslashes. pgslekt=> delete from short_links; DELETE 1 pgslekt=> INSERT INTO short_links (link_type, short_link, long_link, description) VALUES pgslekt-> ('sk', E'\\[sk=(.+?)\\|(.+?)\\|(.+?)\\]', pgslekt(> E'\\3', pgslekt(> 'Scanned probate registers [sk=protocol|image reference|link text]'); INSERT 0 1 pgslekt=> select regexp_replace((select source_text from sources where source_id=23091), (select short_link from long_link from short_links where link_type = 'sk'), 'g'); regexp_replace ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- side 66a. Vabakken under Klyve vestre i Solum 07.07.1784: [p=6947|Isach Jonsen]. (1 row) Beautiful. Now I'm going replace this big ugly function with a loop reading values from a table. I may even write an interface for managing shortlinks :) regards, -- Leif Biberg Kristensen http://solumslekt.org/ From leif@solumslekt.org Tue Apr 27 14:19:13 2010 Received: from maia.hub.org (maia-1.hub.org [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 6E696632C2E for ; Tue, 27 Apr 2010 11:19:13 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 23561-03 for ; Tue, 27 Apr 2010 14:18:53 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail45.e.nsc.no (mail45.e.nsc.no [193.213.115.45]) by mail.postgresql.org (Postfix) with ESMTP id 434E963279B for ; Tue, 27 Apr 2010 11:19:01 -0300 (ADT) Received: from balapapa.localnet (42.185.16.62.customer.cdi.no [62.16.185.42]) (authenticated bits=0) by mail45.nsc.no (8.14.3/8.14.3) with ESMTP id o3REIwUs001425 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT) for ; Tue, 27 Apr 2010 16:18:59 +0200 (MEST) From: Leif Biberg Kristensen To: pgsql-sql@postgresql.org Subject: Re: regexp_replace and search/replace values stored in table Date: Tue, 27 Apr 2010 16:18:57 +0200 User-Agent: KMail/1.12.4 (Linux/2.6.32-gentoo-r7; KDE/4.3.5; i686; ; ) References: <201004271343.48070.leif@solumslekt.org> <201004271532.20639.leif@solumslekt.org> In-Reply-To: <201004271532.20639.leif@solumslekt.org> MIME-Version: 1.0 Content-Type: Text/Plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Message-Id: <201004271618.57658.leif@solumslekt.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.8 tagged_above=-10 required=5 tests=BAYES_50=0.8 X-Spam-Level: X-Archive-Number: 201004/100 X-Sequence-Number: 34027 Followup. Replaced Big Ugly Function with: CREATE OR REPLACE FUNCTION _my_expand(TEXT) RETURNS TEXT AS $$ -- private func, expand various compact links DECLARE str TEXT = $1; links RECORD; BEGIN FOR links IN SELECT short_link, long_link FROM short_links LOOP str := REGEXP_REPLACE(str, links.short_link, links.long_link, 'g'); END LOOP; RETURN str; END $$ LANGUAGE plpgsql IMMUTABLE; By the way, those who haven't read this gem should probably do so: regards, -- Leif Biberg Kristensen http://solumslekt.org/ From leif@solumslekt.org Sat May 1 17:29:16 2010 Received: from maia.hub.org (maia-4.hub.org [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 7C34C632DF3 for ; Sat, 1 May 2010 14:29:16 -0300 (ADT) 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 40721-04 for ; Sat, 1 May 2010 17:29:09 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail44.e.nsc.no (mail44.e.nsc.no [193.213.115.44]) by mail.postgresql.org (Postfix) with ESMTP id 9D30F6326E2 for ; Sat, 1 May 2010 14:29:07 -0300 (ADT) Received: from balapapa.localnet (42.185.16.62.customer.cdi.no [62.16.185.42]) (authenticated bits=0) by mail44.nsc.no (8.14.3/8.14.3) with ESMTP id o41HT4Ff003084 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT) for ; Sat, 1 May 2010 19:29:05 +0200 (MEST) From: Leif Biberg Kristensen To: pgsql-sql@postgresql.org Subject: Re: regexp_replace and search/replace values stored in table Date: Sat, 1 May 2010 19:29:03 +0200 User-Agent: KMail/1.12.4 (Linux/2.6.32-gentoo-r7; KDE/4.3.5; i686; ; ) References: <201004271343.48070.leif@solumslekt.org> In-Reply-To: <201004271343.48070.leif@solumslekt.org> MIME-Version: 1.0 Content-Type: Text/Plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Message-Id: <201005011929.03854.leif@solumslekt.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.8 tagged_above=-5 required=5 tests=BAYES_50=0.8 X-Spam-Level: X-Archive-Number: 201005/3 X-Sequence-Number: 34051 Followup II: I've written a blog post on the subject at . regards, -- Leif Biberg Kristensen http://solumslekt.org/ From Yuval_Sofer@bmc.com Sun May 2 07:25:22 2010 Received: from maia.hub.org (maia-1.hub.org [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 82258632190 for ; Sun, 2 May 2010 04:25:22 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 45632-09 for ; Sun, 2 May 2010 07:25:11 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from flicker.bmc.com (rubyred.bmc.com [198.147.195.5]) by mail.postgresql.org (Postfix) with SMTP id CCDC663336A for ; Sun, 2 May 2010 04:25:14 -0300 (ADT) Received: by flicker.bmc.com (Postfix, from userid 13749) id E6E251E70B0; Sun, 2 May 2010 00:25:12 -0700 (MST) Received: from PHX-HTPRD-02.adprod.bmc.com (phx-htprd-02.adprod.bmc.com [172.24.32.8]) by flicker.bmc.com (Postfix) with ESMTP id DDB131E70AD for ; Sun, 2 May 2010 00:25:12 -0700 (MST) Received: from PHXCCRPRD01.adprod.bmc.com ([172.24.32.5]) by PHX-HTPRD-02.adprod.bmc.com ([172.24.32.8]) with mapi; Sun, 2 May 2010 02:25:12 -0500 From: "Sofer, Yuval" To: "pgsql-sql@postgresql.org" CC: "Primor, Shay" Date: Sun, 2 May 2010 02:25:11 -0500 Subject: Re: regexp_replace and search/replace values stored in table Thread-Topic: [SQL] regexp_replace and search/replace values stored in table Thread-Index: AcrpU9cRZ2OSPCibTzOv4G1leFsXEwAdISOA Message-ID: <2C0926ABD16BB641A8E2F11A5492004227832AFF74@PHXCCRPRD01.adprod.bmc.com> References: <201004271343.48070.leif@solumslekt.org> <201005011929.03854.leif@solumslekt.org> In-Reply-To: <201005011929.03854.leif@solumslekt.org> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201005/4 X-Sequence-Number: 34052 Hi=20 Postgres crashes with - PG "FATAL: could not reattach to shared memory (key=3D5432001, addr=3D0210= 0000): Invalid argument. The version is 8.2.4, the platform is win32 Does someone know the reason/workaround ?=20 Thanks,=20 Yuval Sofer BMC Software CTM&D Business Unit DBA Team 972-52-4286-282 yuval_sofer@bmc.com -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org= ] On Behalf Of Leif Biberg Kristensen Sent: Saturday, May 01, 2010 8:29 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] regexp_replace and search/replace values stored in table Followup II: I've written a blog post on the subject at=20 . =20 regards, --=20 Leif Biberg Kristensen http://solumslekt.org/ --=20 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql