Обсуждение: 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
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
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
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';
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@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 > '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
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/
Hi Leif! Thank you to you and the group. Worked like a charm. The SQL language was the key.... Shawn On Fri, 2009-02-20 at 15:12 +0100, Leif B. Kristensen wrote: > 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; -- 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
On Friday 20. February 2009, Adrian Klaver wrote: >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 I see the documentation, but empirically you don't need the semicolon after the END. About twenty years ago I wrote a lot of Turbo Pascal code, and IIRC semicolon after an END was allowed but considered bad style. -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/
On Friday 20 February 2009 6:29:43 am Leif B. Kristensen wrote: > On Friday 20. February 2009, Adrian Klaver wrote: > >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 > > I see the documentation, but empirically you don't need the semicolon > after the END. > > About twenty years ago I wrote a lot of Turbo Pascal code, and IIRC > semicolon after an END was allowed but considered bad style. Learned something new. I went and reread the docs and found: "Each declaration and each statement within a block is terminated by a semicolon. A block that appears within another block must have a semicolon after END, as shown above; however the final END that concludes a function body does not require a semicolon." I have always seen them terminated with a semicolon and did that assuming thing. -- Adrian Klaver aklaver@comcast.net
On Friday 20. February 2009, Adrian Klaver wrote: >On Friday 20 February 2009 6:29:43 am Leif B. Kristensen wrote: >> About twenty years ago I wrote a lot of Turbo Pascal code, and IIRC >> semicolon after an END was allowed but considered bad style. The rules concerning ENDs and semicolons in Pascal were quite more complex than that. I won't try to make a synopsis here as it's massively off-topic. These days I prefer languages with curly braces as block delimiters; however the old Pascal exposure does come back when I write sprocs in Postgres. Actually, I believe that the syntax is derived from ALGOL, the grandmother of all structured languages, rather than Pascal. >Learned something new. I went and reread the docs and found: > >"Each declaration and each statement within a block is terminated by a >semicolon. A block that appears within another block must have a > semicolon after END, as shown above; however the final END that > concludes a function body does not require a semicolon." > >I have always seen them terminated with a semicolon and did that > assuming thing. «Assumption is the mother of all fuckups» :-) IMHO, someone should revise the syntax of the example in the document (http://www.postgresql.org/docs/current/interactive/plpgsql-structure.html) to state that the semicolon after the final END is not required. -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/