From: Leif Biberg Kristensen <leif@solumslekt.org>
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
Message-ID: <201004271532.20639.leif@solumslekt.org> (raw)
In-Reply-To: <201004271343.48070.leif@solumslekt.org>
References: <201004271343.48070.leif@solumslekt.org>
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'<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]');
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'<a href="//www.arkivverket.no/URN:sk_read/\\1/\\2" title="Lenke
til bilde av skifteprotokollside">\\3</a>',
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
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<a href="//www.arkivverket.no/URN:sk_read/25658/67" title="Lenke til bilde av
skifteprotokollside">side 66a</a>. 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/
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: <201004271532.20639.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