Fwd: Re: Creating a function with single quotes

Memo to self:
Remember hit reply all.

---------- Forwarded Message ----------

Subject: Re: [SQL] Creating a function with single quotes
Date: Friday 20 February 2009
From: Adrian Klaver <aklaver(at)comcast(dot)net>
To: "Leif B. Kristensen" <leif(at)solumslekt(dot)org>

On Friday 20 February 2009 6:13:03 am you wrote:
> On Friday 20. February 2009, Shawn Tayler wrote:
> >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?
>
> You should place a semicolon at the end of the RETURN line, and remove
> the one after END,
>
> BTW, simple functions as this are better written in the SQL language. I
> can't speak for the validity of the code itself, but you can rewrite it
> as
>
> create function f_csd_interval(integer) returns interval as $$
> SELECT $1 * interval '1 msec'
> $$ LANGUAGE SQL;
> --
> Leif Biberg Kristensen | Registered Linux User #338009
> Me And My Database: http://solumslekt.org/blog/

Actually you need both semicolons. One after the RETURN statement and one after
the END statement
See below for full details:
http://www.postgresql.org/docs/8.3/interactive/plpgsql-structure.html

--
Adrian Klaver
aklaver(at)comcast(dot)net

-------------------------------------------------------

--
Adrian Klaver
aklaver(at)comcast(dot)net