[SQL] regexp_replace and search/replace values stored in table

2010-04-27 Thread Leif Biberg Kristensen 
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/

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] regexp_replace and search/replace values stored in table

2010-04-27 Thread Tim Landscheidt 
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


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] regexp_replace and search/replace values stored in table

2010-04-27 Thread Leif Biberg Kristensen 
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/

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] regexp_replace and search/replace values stored in table

2010-04-27 Thread Leif Biberg Kristensen 
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/

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] regexp_replace and search/replace values stored in table

2010-04-27 Thread Leif Biberg Kristensen 
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/

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql