using variable in nextval

From: George Wadsworth <georgew(at)agritope(dot)com>
To: pgsql-sql(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: using variable in nextval
Date: 1999-05-28 00:44:12
Message-ID: 374DE6DC.E8D30DD@agritope.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-sql

Hi!
I'm trying to use a variable as the sequence name in a nextval:
using pgsql 6.4.2, PL/pgSQL called from a trigger

-- site is a char() used to describe the location
-- generation is an int used for generations of plants
-- each site/generation combo has it's own sequence
site := new.site;
gen := new.generation;
sequencename := site || gen;
newSerial := nextval(sequencename);

I get ERROR: Only constant sequence names are acceptable for function
'nextval'

I've tried all sorts of quote, double quotes, etc and this is the
closest I've got.
Does anyone know of a workaround? There are 12 different combinations
and I don't really want to make a big, nasty, nested if...
thanks in advance.

-George Wadsworth

Browse pgsql-general by date

  From Date Subject
Next Message Sebestyen Zoltan 1999-05-28 09:06:46 Re: [GENERAL] 6.4.2 on DEC Alpha
Previous Message carlino 1999-05-27 23:47:31 Couldn't find any tables!

Browse pgsql-sql by date

  From Date Subject
Next Message Ivanko Alexandr 1999-05-28 06:04:50 (no subject)
Previous Message Mark Wright 1999-05-27 23:12:17 How do I implement FIFO?