Re: Creating a function with single quotes

Hello Jasen and the List,

I tried the $$ quote suggestion:

create function f_csd_interval(integer) returns interval as 
$$
BEGIN
RETURN $1 * interval '1 msec'
END;
$$
LANGUAGE 'plpgsql';

Here is what I got:

edacs=# \i 'f_csd_interval.sql'
psql:f_csd_interval.sql:7: ERROR:  syntax error at or near "END"
LINE 1: SELECT  ( $1  * interval '1 msec') END                                          ^
QUERY:  SELECT  ( $1  * interval '1 msec') END
CONTEXT:  SQL statement in PL/PgSQL function "f_csd_interval" near line2
edacs=#

The error at or near END is curious.  There must be something wrong in
the line before it but I can't see it.  Suggestions?

In case it matters, the server is v8.2.11 compiled from source on
Slackware 11.0 and the terminal is v8.3.6 running on Ubuntu v8.10.



On Fri, 2009-02-20 at 08:11 +0000, Jasen Betts wrote:
> On 2009-02-19, Shawn Tayler <stayler@washoecounty.us> wrote:
> > Hello,
> >
> > This has me befuddled.  I am trying create a simple experiment, rather
> > new to SQL and I am running into an issue with single quotes.  All I can
> > find on creating a function states the procedure should be contained
> > within single quotes.  My problem comes when I want to use a textual
> > representation of an interval.
> >
> > create function csd_interval(integer) returns interval as 
> > 'BEGIN
> > RETURN $1 * interval '1 msec'
> > END;'
> > LANGUAGE 'plpgsql';
> >
> > it always fails at the '1 msec' point.
> >
> > Suggestions?
> 
> you need to quote the inner quotes,
> 
>  create function csd_interval(integer) returns interval as 
>  'BEGIN
>  RETURN $1 * interval ''1 msec''
>  END;'
>  LANGUAGE 'plpgsql';
> 
> when the function itself uses single quotes in literals this quickly
> becomes confusing, and so "dollar quoting" was invented.
> 
>  create function csd_interval(integer) returns interval as 
>  $$BEGIN
>  RETURN $1 * interval '1 msec'
>  END;$$
>  LANGUAGE 'plpgsql';
> 
-- 
Sincerely,

Shawn Tayler
Radio Network Administrator
Washoe County Regional Communications System
Telecommunications Division
Technology Services Department
County of Washoe
State of Nevada
Ofc  (775)858-5952
Cell (775)771-4241
FAX  (775)858-5960