Received: from maia.hub.org (maia-4.hub.org [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id DD8B2632971
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	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
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	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 <pgsql-sql@postgresql.org>; 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 <pgsql-sql@postgresql.org>; Tue, 27 Apr 2010 13:43:48 +0200 (MEST)
From: Leif Biberg Kristensen <leif@solumslekt.org>
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'<a 
href="//www.arkivverket.no/URN:kb_read?idx_kildeid=\\1&amp;uid=ny&amp;idx_side=\\2" 
title="Lenke til bilde av kirkebokside">\\3</a>', 'g');
    -- Scanned probate registers [sk=protocol|image reference|link text]
    str := REGEXP_REPLACE(str, E'\\[sk=(.+?)\\|(.+?)\\|(.+?)\\]',
            E'<a href="//www.arkivverket.no/URN:sk_read/\\1/\\2" title="Lenke 
til bilde av skifteprotokollside">\\3</a>', 'g');
    -- Scanned deed ("pantebok") registers [sk=protocol|image reference|link 
text]
    str := REGEXP_REPLACE(str, E'\\[tl=(.+?)\\|(.+?)\\|(.+?)\\]',
            E'<a 
href="//www.arkivverket.no/URN:tl_read?idx_id=\\1&amp;uid=ny&amp;idx_side=\\2" 
title="Lenke til bilde av pantebokside">\\3</a>', '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'<a href="//www.arkivverket.no/URN:sk_read/\\\\1/\\\\2" title="Lenke til 
bilde av skifteprotokollside">\\\\3</a>',
'Scanned probate registers [sk=protocol|image reference|link text]');

pgslekt=> select * from short_links;

 link_type |           short_link           |                                                 
long_link                                                 |                            
description
-----------+--------------------------------+-----------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------
 sk        | \\[sk=(.+?)\\|(.+?)\\|(.+?)\\] | <a 
href="//www.arkivverket.no/URN:sk_read/\\1/\\2" title="Lenke til bilde av 
skifteprotokollside">\\3</a> | 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/