Re: Postgres 8.4 literal escaping

Andreas escribió:
> Hi,
> I've got a similar issue with a function that uses regular-expression-magic.
> I got it from the sql list and it works but I'm just about 75% aware of how.
> Still PG complains about those \\ everywhere.
>
> Replacing every \ by || E'\\' || would make it ... cough ... not
> looking cuter as it allready is.
> What would be the correct syntax here?

Just prepend E to the whole string, i.e. instead of
'\\(0\\)||\\(||\\)' use E'\\(0\\)||\\(||\\)'

If you're list most people and hate having to double the \s use
dollar-quoting, as shown below.

BTW it seems the function could be written in the SQL language instead
of plpgsql, which could make it faster.

> CREATE OR REPLACE FUNCTION cleanphonenr(text)
> RETURNS text AS
> $BODY$
> BEGIN
> RETURN CASE
> WHEN regexp_replace($1, '[^0-9+]', '', 'g') ~ $$^(\+|00)49$$
> THEN '0'||
> regexp_replace(
> regexp_replace(
> regexp_replace($1, '[^0-9+()]', '', 'g')
> , $$\(0\)||\(||\)$$, '', 'g')
> , $$^(?:\+|00)49(.*)$$, $$\1$$)
> WHEN regexp_replace($1, '[^0-9+]', '', 'g') ~ $$^(\\+|00)$$
> THEN '+'||
> regexp_replace(
> regexp_replace(
> regexp_replace($1, '[^0-9+()]', '', 'g')
> , $$\(0\)||\(||\)$$, '', 'g')
> , $$^(?:\+||00)(.*)$$, $$\1$$)
> ELSE
> regexp_replace($1, E'[^0-9]', '', 'g')
> END;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
> COST 100;

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support