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
Message-ID: <201004271343.48070.leif@solumslekt.org> (raw)
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&uid=ny&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&uid=ny&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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: leif@solumslekt.org
Subject: Re: regexp_replace and search/replace values stored in table
In-Reply-To: <201004271343.48070.leif@solumslekt.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox