From: | "Jonathon Batson" <jonathon(at)octahedron(dot)com(dot)au> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Sequences Question |
Date: | 2002-10-25 22:34:59 |
Message-ID: | 001601c27c76$c4f438c0$5005a8c0@duron |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | John Ragan | 2002-10-26 03:32:00 | CoreReader |
Previous Message | Chad Thompson | 2002-10-25 19:21:11 | Re: Select case |