Обсуждение: Newbie question about escaping in a function

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@lfix.co.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

Thanks! Now I get it...

naeem

-----Original Message-----
From: Oliver Elphick [mailto:olly@lfix.co.uk]
Sent: Tuesday, October 26, 2004 3:05 PM
To: Naeem Bari
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Newbie question about escaping in a function

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@lfix.co.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

Try using EXECUTE.

http://www.postgresql.org/docs/7.4/static/plpgsql-
statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Oct 26, 2004, at 11:57 AM, 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;
>
>  
>
>   select tdat + interval ''$2 $3'' into rdat;
>
>   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.
>
>  
>
> Help! J
>
>  
>
> Thanks,
>
> naeem