[SQL] Creating a function with single quotes
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, 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 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Creating a function with single quotes
On Feb 19, 2009, at 1:41 PM, 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. Try using: create function csd_interval(integer) returns interval as $$ BEGIN RETURN $1 * interval '1 msec' END; $$ LANGUAGE 'plpgsql'; For more info refer to section 4.1.2.2. Dollar-Quoted String Constants in the manual. Agustin -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Fwd: Re: [SQL] 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 To: [email protected] 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 'BEGIN RETURN $1 * interval ''1 msec'' END;' LANGUAGE 'plpgsql'; Two, better to use dollar quoting create function csd_interval(integer) returns interval as $$ BEGIN RETURN $1 * interval '1 msec' END; $$ LANGUAGE 'plpgsql'; See http://www.postgresql.org/docs/8.3/interactive/plpgsql-development-tips.html -- Adrian Klaver [email protected] --- -- Adrian Klaver [email protected] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Creating a function with single quotes
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. The documentation for PL/PgSQL goes into quoting in great depth. Read that if you want to understand. However, the quick and dirty answer is to use $$ style quoting: create function csd_interval(integer) returns interval as $$ BEGIN RETURN $1 * interval '1 msec' END; $$ LANGUAGE 'plpgsql'; -- Craig Ringer -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
