Re: Creating a function with single quotes

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 'BEGINRETURN $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 $$BEGINRETURN $1 * interval '1 msec'END;$$LANGUAGE
'plpgsql';