Fwd: Re: Creating a function with single quotes

Oops, forgot to reply to list.

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

Subject: Re: [SQL] Creating a function with single quotes
Date: Thursday 19 February 2009
From: Adrian Klaver <aklaver@comcast.net>
To: stayler@washoecounty.us

On Thursday 19 February 2009 7:41:11 am Shawn Tayler 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?
> --
> Sincerely,
>
>

Two suggestions 
One, double the quotes

create function csd_interval(integer) returns interval as'BEGINRETURN $1 * interval ''1 msec''END;'LANGUAGE 'plpgsql';

Two, better to use dollar quoting

create function csd_interval(integer) returns interval as$$BEGINRETURN $1 * interval '1 msec'END;$$LANGUAGE 'plpgsql';

See
http://www.postgresql.org/docs/8.3/interactive/plpgsql-development-tips.html

-- 
Adrian Klaver
aklaver@comcast.net

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

-- 
Adrian Klaver
aklaver@comcast.net