Thread: Creating a function with single quotes

Creating a function with single quotes

From
Shawn Tayler
Date:
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



Re: Creating a function with single quotes

From
Agustin Bialet
Date:
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


Re: Creating a function with single quotes

From
Craig Ringer
Date:
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


Re: Creating a function with single quotes

From
Jasen Betts
Date:
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';


Re: Creating a function with single quotes

From
Shawn Tayler
Date:
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



Re: Creating a function with single quotes

From
"Leif B. Kristensen"
Date:
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/


Re: Creating a function with single quotes

From
Shawn Tayler
Date:
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



Re: Creating a function with single quotes

From
"Leif B. Kristensen"
Date:
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/


Re: Creating a function with single quotes

From
Adrian Klaver
Date:
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


Re: Creating a function with single quotes

From
"Leif B. Kristensen"
Date:
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/