Newbie ...Function error (Stored Procedure)?

You have to escape the ' inside of your function definition:

CREATE FUNCTION testid()
RETURNS INTEGER
AS 'SELECT nextval(\'seq1\');'
LANGUAGE 'SQL';

The quotationmark in front of seq1 terminated the literal string that
should contain your
SQL statement and the parser was confused to find an 's' behind the
literal string.

Arne.

On Mon, Aug 27, 2001 at 12:34:14PM -0700, Ron S wrote:

I have a sequence called seq1.

In psql I am trying to create a function which simply calls this
sequence with the nextval() function.

CREATE FUNCTION testid()
RETURNS INTEGER
AS 'SELECT NEXTVAL('seq1');'
LANGUAGE 'SQL';

I get the following error....
ERROR: parser: parser error at or near "seq1"

I can call nextval('seq1') by itself with now error.

What am I doing wrong?

The first single quote after the left parenthesis is seen as ending
the function body, so postgres looks for the keyword LANGUAGE
immediately afterwards. Double your single quotes inside the function
body or backslash them:

CREATE FUNCTION testid()
RETURNS INTEGER
AS 'SELECT NEXTVAL(''seq1'');'
LANGUAGE 'SQL';

or

CREATE FUNCTION testid()
RETURNS INTEGER
AS 'SELECT NEXTVAL(\'seq1\');'
LANGUAGE 'SQL';

Richard

I think you need to double the single quotes around the sequence name:
(''seq1'').
Statements inside function definitions go through an extra level of parsing,
which strips off one set of single quotes.

----- Original Message -----
From: "Ron S" <zhi7c001@sneakemail.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, August 27, 2001 2:34 PM
Subject: [GENERAL] Newbie ...Function error (Stored Procedure)?