From: | Oliver Elphick <olly(at)lfix(dot)co(dot)uk> |
---|---|
To: | Naeem Bari <naeem(dot)bari(at)agilissystems(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Newbie question about escaping in a function |
Date: | 2004-10-26 20:04:30 |
Message-ID: | 1098821070.2414.30.camel@linda |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, 2004-10-26 at 11:57 -0500, Naeem Bari wrote:
> I have a simple function defined thusly:
>
>
>
> CREATE OR REPLACE FUNCTION datemath(timestamp with time zone, int4,
> varchar)
>
> RETURNS timestamp AS
>
> '
>
> DECLARE
>
> tdat timestamp;
>
> rdat timestamp;
>
> BEGIN
>
> IF ($1 IS NULL) THEN
>
> TDAT := NOW();
>
> ELSE
>
> TDAT := $1;
>
> END IF;
It's neater to use the COALESCE() function, which is designed expressly
for this.
> select tdat + interval ''$2 $3'' into rdat;
In PL/pgSQL that should be "select into rdat ..."; but that won't work
in any case because you can't use passed parameters inside a string like
that.
> return rdat;
>
> END;
>
> '
>
> LANGUAGE 'plpgsql' VOLATILE;
>
>
>
> The problem is the interval part. How do I tell the bugger to use the
> second and third params as input to interval? I have tried different
> ways of escaping, from \’$2 $3\’ to ‘’$2 $3’’ and everything else in
> between, it just doesn’t like it.
You have to construct a command string and use EXECUTE:
CREATE OR REPLACE FUNCTION datemath(timestamp with time zone, int4, varchar)
RETURNS timestamp AS
'DECLARE
tdat TIMESTAMP;
result RECORD;
cmd TEXT;
BEGIN
tdat := COALESCE($1, NOW());
cmd := ''SELECT '' || quote_literal(tdat) ||
''::TIMESTAMP + INTERVAL '' ||
quote_literal($2 || '' '' || $3) || '' AS x'';
FOR result IN EXECUTE cmd LOOP
return result.x;
END LOOP;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
--
Oliver Elphick olly(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"Whosoever therefore shall be ashamed of me and of my
words in this adulterous and sinful generation; of him
also shall the Son of man be ashamed, when he cometh
in the glory of his Father with the holy angels."
Mark 8:38
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2004-10-26 20:04:37 | Re: Error restoring bytea from dump |
Previous Message | Dan Sugalski | 2004-10-26 19:59:15 | Re: Base type OIDs |