Sequences Question

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


Responses

Browse pgsql-novice by date

  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