Re: regexp_replace and search/replace values stored in table

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/