pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPassing function parameters to regexp_replace
5+ messages / 2 participants
[nested] [flat]
* Passing function parameters to regexp_replace
@ 2011-09-17 11:21 Leif Biberg Kristensen <leif@solumslekt.org>
0 siblings, 1 reply; 5+ messages in thread
From: Leif Biberg Kristensen @ 2011-09-17 11:21 UTC (permalink / raw)
To: pgsql-sql
I'm trying to write a sql or plpgsql function update_nametags(TEXT, TEXT)
which does a replace on this form:
UPDATE sources SET source_text = regexp_replace(source_text,
E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2"', 'g') where source_text like
'%n="%$2%">%';
But I can't find out how to escape the parameters $1 and $2. I've also tried to
wrap it in an EXECUTE and concatenate the parameters with no luck.
I'd also like to have the function return the number of affected rows.
regards, Leif
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Passing function parameters to regexp_replace
@ 2011-09-17 11:31 Leif Biberg Kristensen <leif@solumslekt.org>
parent: Leif Biberg Kristensen <leif@solumslekt.org>
0 siblings, 1 reply; 5+ messages in thread
From: Leif Biberg Kristensen @ 2011-09-17 11:31 UTC (permalink / raw)
To: pgsql-sql
On Saturday 17. September 2011 13.21.43 Leif Biberg Kristensen wrote:
> UPDATE sources SET source_text = regexp_replace(source_text,
> E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2"', 'g') where source_text like
> '%n="%$2%">%';
Sorry, I pasted a literal replacement, and substituted the parameters by hand.
The expression should of course be
UPDATE sources SET source_text = regexp_replace(source_text,
E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2"', 'g') where source_text like
'%n="%$1%">%'
regards, Leif
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Passing function parameters to regexp_replace
@ 2011-09-17 17:07 Tim Landscheidt <tim@tim-landscheidt.de>
parent: Leif Biberg Kristensen <leif@solumslekt.org>
0 siblings, 1 reply; 5+ messages in thread
From: Tim Landscheidt @ 2011-09-17 17:07 UTC (permalink / raw)
To: pgsql-sql
Leif Biberg Kristensen <leif@solumslekt.org> wrote:
>> UPDATE sources SET source_text = regexp_replace(source_text,
>> E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2"', 'g') where source_text like
>> '%n="%$2%">%';
> Sorry, I pasted a literal replacement, and substituted the parameters by hand.
> The expression should of course be
> UPDATE sources SET source_text = regexp_replace(source_text,
> E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2"', 'g') where source_text like
> '%n="%$1%">%'
Try:
> UPDATE sources SET source_text = regexp_replace(source_text,
> CONCAT(E'n="(.*?)', $1, E'(.*?)"'), CONCAT(E'n="\\1', $2, '\\2"', 'g') where source_text like
> CONCAT('%n="%', $1, '%">%')
If $1 and $2 (can) include meta characters, you have to es-
cape them properly.
Please consider that regexp_replace() uses POSIX Regular
Expressions while LIKE uses a different syntax. If possible,
I would replace the LIKE expression with its "~" equivalent
so chances of confusion are minimized.
Tim
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Passing function parameters to regexp_replace
@ 2011-09-17 17:27 Leif Biberg Kristensen <leif@solumslekt.org>
parent: Tim Landscheidt <tim@tim-landscheidt.de>
0 siblings, 1 reply; 5+ messages in thread
From: Leif Biberg Kristensen @ 2011-09-17 17:27 UTC (permalink / raw)
To: pgsql-sql
On Saturday 17. September 2011 19.07.03 Tim Landscheidt wrote:
> Leif Biberg Kristensen <leif@solumslekt.org> wrote:
>
> > UPDATE sources SET source_text = regexp_replace(source_text,
> > E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2"', 'g') where source_text like
> > '%n="%$1%">%'
>
> Try:
> > UPDATE sources SET source_text = regexp_replace(source_text,
> > CONCAT(E'n="(.*?)', $1, E'(.*?)"'), CONCAT(E'n="\\1', $2, '\\2"', 'g')
> > where source_text like CONCAT('%n="%', $1, '%">%')
The function CONCAT doesn't exist i PostgreSQL. And I can't get it to work
with EXECUTE and standard concatenation either:
pgslekt=> CREATE OR REPLACE FUNCTION update_nametags(TEXT, TEXT) RETURNS VOID
AS $$
pgslekt$> BEGIN
pgslekt$> EXECUTE $_$
pgslekt$> UPDATE sources SET source_text =
pgslekt$> REGEXP_REPLACE(
pgslekt$> source_text,
pgslekt$> E'n="(.*?)' || $1 || '(.*?)"',
pgslekt$> E'n="\\1' || $2 || '\\2"', 'g'
pgslekt$> )
pgslekt$> WHERE source_text LIKE E'%n="%' || $1 || '%">%'
pgslekt$> $_$;
pgslekt$> END
pgslekt$> $$ LANGUAGE PLPGSQL VOLATILE;
CREATE FUNCTION
Time: 1,105 ms
pgslekt=> select update_nametags('Brynild','Brynil');
WARNING: nonstandard use of \\ in a string literal
LINE 6: E'n="\\1' || $2 || '\\2"', 'g'
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
QUERY:
UPDATE sources SET source_text =
REGEXP_REPLACE(
source_text,
E'n="(.*?)' || $1 || '(.*?)"',
E'n="\\1' || $2 || '\\2"', 'g'
)
WHERE source_text LIKE E'%n="%' || $1 || '%">%'
CONTEXT: PL/pgSQL function "update_nametags" line 2 at EXECUTE statement
ERROR: there is no parameter $1
LINE 5: E'n="(.*?)' || $1 || '(.*?)"',
^
QUERY:
UPDATE sources SET source_text =
REGEXP_REPLACE(
source_text,
E'n="(.*?)' || $1 || '(.*?)"',
E'n="\\1' || $2 || '\\2"', 'g'
)
WHERE source_text LIKE E'%n="%' || $1 || '%">%'
CONTEXT: PL/pgSQL function "update_nametags" line 2 at EXECUTE statement
> If $1 and $2 (can) include meta characters, you have to es-
> cape them properly.
>
> Please consider that regexp_replace() uses POSIX Regular
> Expressions while LIKE uses a different syntax. If possible,
> I would replace the LIKE expression with its "~" equivalent
> so chances of confusion are minimized.
The intended use is to replace a short string like 'Jacob' with 'Jakob' within
a specific XML attribute value.
regards, Leif
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Passing function parameters to regexp_replace
@ 2011-09-17 18:34 Tim Landscheidt <tim@tim-landscheidt.de>
parent: Leif Biberg Kristensen <leif@solumslekt.org>
0 siblings, 0 replies; 5+ messages in thread
From: Tim Landscheidt @ 2011-09-17 18:34 UTC (permalink / raw)
To: pgsql-sql
Leif Biberg Kristensen <leif@solumslekt.org> wrote:
> On Saturday 17. September 2011 19.07.03 Tim Landscheidt wrote:
>> Leif Biberg Kristensen <leif@solumslekt.org> wrote:
>> > UPDATE sources SET source_text = regexp_replace(source_text,
>> > E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2"', 'g') where source_text like
>> > '%n="%$1%">%'
>> Try:
>> > UPDATE sources SET source_text = regexp_replace(source_text,
>> > CONCAT(E'n="(.*?)', $1, E'(.*?)"'), CONCAT(E'n="\\1', $2, '\\2"', 'g')
>> > where source_text like CONCAT('%n="%', $1, '%">%')
> The function CONCAT doesn't exist i PostgreSQL.
Eh, yes, of course.
> And I can't get it to work
> with EXECUTE and standard concatenation either:
> [...]
What do you need the EXECUTE for? Just drop it.
Tim
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-09-17 18:34 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-17 11:21 Passing function parameters to regexp_replace Leif Biberg Kristensen <leif@solumslekt.org>
2011-09-17 11:31 ` Leif Biberg Kristensen <leif@solumslekt.org>
2011-09-17 17:07 ` Tim Landscheidt <tim@tim-landscheidt.de>
2011-09-17 17:27 ` Leif Biberg Kristensen <leif@solumslekt.org>
2011-09-17 18:34 ` Tim Landscheidt <tim@tim-landscheidt.de>
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