Thread: Escaping \

Hello all,

I've a problem with escaping a \ in a string.

When I enter the query:

SELECT '''\\\''; I get the right result: '\'

But when I try this in a Function:

CREATE FUNCTION sp_tmp() RETURNS varchar(10)
AS '       SELECT ''\\\' AS RESULT'
LANGUAGE 'sql';

I get the following Parse-erros:

psql:tmp1:4: ERROR:  Unterminated quoted string. I don't understand why,
when you leave the SELECT-statement out you get '''\\\'' and that is not
unterminated.

Please help

Greetings

Martijn van Dijk

From: "Martijn van Dijk" <martijn@hardworks.nl>

> I've a problem with escaping a \ in a string.
>
> When I enter the query:
>
> SELECT '''\\\''; I get the right result: '\'
>
> But when I try this in a Function:
>
> CREATE FUNCTION sp_tmp() RETURNS varchar(10)
> AS '
>         SELECT ''\\\' AS RESULT'
> LANGUAGE 'sql';
>
> I get the following Parse-erros:
>
> psql:tmp1:4: ERROR:  Unterminated quoted string. I don't understand why,
> when you leave the SELECT-statement out you get '''\\\'' and that is not
> unterminated.

Had something similar myself the other day. The reason is that you are
already one deep in Postgres' string-parser, so you need something like:

SELECT ''\\\\'' AS RESULT

HTH

- Richard Huxton

"Martijn van Dijk" <martijn@hardworks.nl> writes:
> But when I try this in a Function:

> CREATE FUNCTION sp_tmp() RETURNS varchar(10)
> AS '
>         SELECT ''\\\' AS RESULT'
> LANGUAGE 'sql';

> I get the following Parse-erros:

You need an extra level of quoting because the function body is itself
a string literal.  You might find the quoting discussion in
http://www.postgresql.org/devel-corner/docs/postgres/plpgsql-porting.html
helpful.
        regards, tom lane

> > I've a problem with escaping a \ in a string.
> >
> > When I enter the query:
> >
> > SELECT '''\\\''; I get the right result: '\'
> >
> > But when I try this in a Function:
> >
> > CREATE FUNCTION sp_tmp() RETURNS varchar(10)
> > AS '
> >         SELECT ''\\\' AS RESULT'
> > LANGUAGE 'sql';
> >
> > I get the following Parse-erros:
> >
> > psql:tmp1:4: ERROR:  Unterminated quoted string. I don't understand why,
> > when you leave the SELECT-statement out you get '''\\\'' and that is not
> > unterminated.
> 
> Had something similar myself the other day. The reason is that you are
> already one deep in Postgres' string-parser, so you need something like:
> 
> SELECT ''\\\\'' AS RESULT

Seems as if this would give \\

I think you need something like 
 SELECT ''''\\\'' AS RESULT

In other words, double all the single quotes.  (This leads to all kinds of
fun stuff with 7.1 and code generating queries ;)

-Cedar