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(at)washoecounty(dot)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