Thread: Incorrect (?) escape of the $1

Is there any magic sequence of quotes or backslashes that will get the following (simplified version) to do the obvious
thing?

CREATE FUNCTION doesnt_work(integer) RETURNS date LANGUAGE SQL AS
'SELECT (date ''epoch'' + interval '' $1 day'')::date ' STRICT IMMUTABLE;

No variable interpolation is performed; the interval 1 day is always used.

andrew@pillette.com writes:
> Is there any magic sequence of quotes or backslashes that will get the following (simplified version) to do the
obviousthing? 
> CREATE FUNCTION doesnt_work(integer) RETURNS date LANGUAGE SQL AS
> 'SELECT (date ''epoch'' + interval '' $1 day'')::date ' STRICT IMMUTABLE;

> No variable interpolation is performed; the interval 1 day is always used.

You could probably make it work via string concatenation (||)
if you were absolutely intent on it, but far easier is to use the
number-times-interval operator:

CREATE FUNCTION does_work(integer) RETURNS date LANGUAGE SQL AS
'SELECT (date ''epoch'' + $1 * interval ''1 day'')::date'
STRICT IMMUTABLE;

            regards, tom lane

|| does NOT work but your solution is so much nicer anyway.

Thanks. Very clever.

Tom Lane <tgl@sss.pgh.pa.us> wrote ..
> andrew@pillette.com writes:
> > Is there any magic sequence of quotes or backslashes that will get the
> following (simplified version) to do the obvious thing?
> > CREATE FUNCTION doesnt_work(integer) RETURNS date LANGUAGE SQL AS
> > 'SELECT (date ''epoch'' + interval '' $1 day'')::date ' STRICT IMMUTABLE;
>
> > No variable interpolation is performed; the interval 1 day is always
> used.
>
> You could probably make it work via string concatenation (||)
> if you were absolutely intent on it, but far easier is to use the
> number-times-interval operator:
>
> CREATE FUNCTION does_work(integer) RETURNS date LANGUAGE SQL AS
> 'SELECT (date ''epoch'' + $1 * interval ''1 day'')::date'
> STRICT IMMUTABLE;
>
>             regards, tom lane