Thread: how many quotes?

I need a function to fetch the nextval of a sequence (it should do other things, but my problem is the nextval() call), like this:

CREATE OR REPLACE FUNCTION test() RETURNS INTEGER AS '
DECLARE
   val INTEGER;
BEGIN
   val:=SELECT nextval(''''''''''usuarios_seq'''''''''');
  
   --do other things
END;
' LANGUAGE 'plpgsql';

SELECT test();
ERROR:  syntax error at or near "SELECT" at character 8
CONTEXT:  PL/pgSQL function "test" line 4 at assignment

I can't figure out how many quotes I should use to call nextval. I've read an article at the techdocs about this, but didn't help me. If anyone knows the *magic* number, please tell me

thanks in advance.

On Tuesday 02 December 2003 16:12, Franco Bruno Borghesi wrote:
> I need a function to fetch the nextval of a sequence (it should do other
> things, but my problem is the nextval() call), like this:
>
> CREATE OR REPLACE FUNCTION test() RETURNS INTEGER AS '
> DECLARE
>    val INTEGER;
> BEGIN
>    val:=SELECT nextval(''''''''''usuarios_seq'''''''''');

> I can't figure out how many quotes I should use to call nextval. I've
> read an article at the techdocs about this, but didn't help me. If
> anyone knows the *magic* number, please tell me :)

Two, or escape them:
  nextval(''usuarios_seq'')
or
  nextval(\'usuarios_seq\')

--
  Richard Huxton
  Archonet Ltd

I've found the problem, its not about the quotes, it's the way the assignment is made:
val:=SELECT nextval(\'usuarios_seq\'); <-WRONG
val:=nextval(\'usuarios_seq\'); <-RIGHT

SELECT INTO should work too.

Thanks everyone.

On Tue, 2003-12-02 at 14:12, Richard Huxton wrote:

On Tuesday 02 December 2003 16:12, Franco Bruno Borghesi wrote:
> I need a function to fetch the nextval of a sequence (it should do other
> things, but my problem is the nextval() call), like this:
>
> CREATE OR REPLACE FUNCTION test() RETURNS INTEGER AS '
> DECLARE
>    val INTEGER;
> BEGIN
>    val:=SELECT nextval(''''''''''usuarios_seq'''''''''');

> I can't figure out how many quotes I should use to call nextval. I've
> read an article at the techdocs about this, but didn't help me. If
> anyone knows the *magic* number, please tell me :)

Two, or escape them: nextval(''usuarios_seq'')
or nextval(\'usuarios_seq\')