Received: from nt_agritope.agritope.com (nt-agritope.agritope.com [208.151.232.34]) by hub.org (8.9.3/8.9.3) with ESMTP id UAA82027; Thu, 27 May 1999 20:45:36 -0400 (EDT) (envelope-from georgew@agritope.com) Received: from agritope.com (DOGBERT [200.200.200.99]) by nt_agritope.agritope.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2232.9) id 27RS3CDN; Thu, 27 May 1999 17:48:04 -0700 Sender: postgres Message-ID: <374DE6DC.E8D30DD@agritope.com> Date: Thu, 27 May 1999 17:44:12 -0700 From: George Wadsworth Organization: Agritope X-Mailer: Mozilla 4.07 [en] (X11; I; Linux 2.0.36 i686) MIME-Version: 1.0 To: pgsql-sql@postgresql.org, pgsql-general@postgresql.org Subject: using variable in nextval Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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