Hi
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
