Home > mailing lists

Thread: Sequences Question

Sequences Question

From

"Jonathon Batson"

Date:

28 October 2002, 11:14:29

I am converting a large DB from MSAccess and would like to programatically set sequences(nextval) to the last value + 1

of that field (for every sequence eventually). I can use for eg at command prompt

SELECT setval('seq_name', 400);

to do this but I want to eventually do this for 30 sequences. So playing with pgsql I came up with this for starters

CREATE OR REPLACE FUNCTION set_interview_max_seq() RETURNS INT4 AS'
DECLARE
-- Declare a variable to hold the max sequence ID number

-- Declare a variable to return the next sequence ID number
max_seq INTEGER;
next_seq INTEGER;

   BEGIN
      SELECT INTO max_seq max(id) from interview;
      SELECT setval("interview_id_seq", max_seq);
      SELECT INTO next_seq select nextval("interview_id_seq");
      RETURN next_seq;
   END
'language 'plpgsql';

upon running the function the following error occurs

# select set_interview_max_seq();

NOTICE: Error occurred while executing PL/pgSQL function set_interview_max_seq

NOTICE: line 8 at SQL statement

ERROR: Attribute 'interview_id_seq' not found

1> the seq is definately there

2> syntax is a question??

so I also tried removing the " " around both instances of the sequence name

and received the same error. Also tried removing all quotes but then a parse error occurs.

Help thankfully accepted

Re: Sequences Question

From

"Josh Berkus"

Date:

28 October 2002, 15:03:50

Jonathon,

> 1> the seq is definately there
> 2> syntax is a question??
> so I also tried removing the " " around both instances of the
> sequence
> name
> and received the same error.  Also tried removing all quotes but then
> a
> parse error occurs.

Real simple ... within a function, you need to use a
double-single-quote, '' , rather than a double-quote ".

PostgreSQL only uses double-quotes for "quoted identifiers", that is
database object names which are not allowed, such as "Table One" and
"1st_try".  All other quoting purposes are fulfilled by single quotes
'.

-Josh