Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

Next:From: John RaganDate: 2002-10-26 03:32:00
Subject: CoreReader
Previous:From: Chad ThompsonDate: 2002-10-25 19:21:11
Subject: Re: Select case

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group