Postgres Pro
Downloads
Home   >   mailing lists

Thread: Newbie ...Function error (Stored Procedure)?

Newbie ...Function error (Stored Procedure)?

From
zhi7c001@sneakemail.com (Ron S)
Date:
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? I can't for the life of me figure this
seemingly simple
error out ;)

Postgresql 7.0.3 on Mandrake 8.0

Thanks,
Ron

Re: Newbie ...Function error (Stored Procedure)?

From
Richard Poole
Date:
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

Re: Newbie ...Function error (Stored Procedure)?

From
"Jeff Eckermann"
Date:
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)?


> 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? I can't for the life of me figure this
> seemingly simple
> error out ;)
>
> Postgresql 7.0.3 on Mandrake 8.0
>
> Thanks,
> Ron
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>