Thread: Postgres 8.4 literal escaping

with Postgres 8.4:

SQL 1: update customer set note = ('A' || '\r\n' || 'B') where 1=0;

generates:
WARNING:  nonstandard use of escape in a string literal
LINE 1: update customer set note = ('A' || '\r\n' || 'B') where 1=0;
                                           ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
(Note is of datatype TEXT)

SQL 2: select ('A' || '\r\n' || 'B') as tt from customer limit 1
Functions without escaping literal

On postgres 8.3.7 both of the above SQLs function without requiring
any literals to be escaped.

This seems inconsistent to me in that I would have thought that in
Postgres 8.4 either both SQLs would have required escaping or neither
of them would have required escaping.

Niederland <niederland@gmail.com> writes:
> SQL 2: select ('A' || '\r\n' || 'B') as tt from customer limit 1
> Functions without escaping literal

Really?  I get

regression=# select ('A' || '\r\n' || 'B') as tt from customer limit 1;
WARNING:  nonstandard use of escape in a string literal
LINE 1: select ('A' || '\r\n' || 'B') as tt from customer limit 1;
                       ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
 tt
----
(0 rows)

            regards, tom lane

On Jul 9, 2:18 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> Niederland <niederl...@gmail.com> writes:
> > SQL 2: select ('A' || '\r\n' || 'B') as tt from customer limit 1
> > Functions without escaping literal
>
> Really?  I get
>
> regression=# select ('A' || '\r\n' || 'B') as tt from customer limit 1;
> WARNING:  nonstandard use of escape in a string literal
> LINE 1: select ('A' || '\r\n' || 'B') as tt from customer limit 1;
>                        ^
> HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
>  tt
> ----
> (0 rows)
>
>                         regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Verified your results via psql, you were correct as always.

I was using pgadmin and did not realize after that running the select
query the pgadmin automatically showed the Data Output tab (and the
escape warning was in the hidden Messages tab), but when runing the
update query the pgadmin jumped to the Messages tab showing the escape
warning).

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?

I need it to search phone numbers.
The function strips all characters that are no number or "+" out of the
input-string.
If the international part is "+49" or "0049" it get reduced to 0.


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

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