November 23rd, 2012, 12:05 PM
Retriving nextval(sequence) in a variable
I am new to POSTGRES and am having trouble in retrieving a sequence next value into a declared variable within a function. Mu function complies OK - but when I call it I get a column does not exist error.
The sequence is defined as:
create sequence act_individual_id_seq
start with 300
increment by 1 ;
When I use the following statement directly it return a correct value:
select nextval(act_individual_id_seq) ;
My function is:
CREATE OR REPLACE FUNCTION staff_add(int, varchar, varchar, varchar, varchar, varchar, int, int, varchar, varchar, varchar, varchar, varchar, varchar) RETURNS void AS
v_nextSeq int ;
select nextval(act_individual_id_seq) AS v_nextSeq ;
-- v_nextSeq=values(NEXTVAL for act_individual_id_seq) ;
Insert Into act_individual( id,typ, sal_id, first_name,
office_tel, mobile, email,
values (v_nextSeq, 2, $1, $2, $3, $4, $5, $9,
$10, $11, $12, $13, $14, true,
Insert Into act_staff( id, staff_no, unit_id,
values ( v_nextSeq, $6, $7, $8, true) ;
I am trying to put the nextval into the variable v_nextVal but it gives the 'act_individual_id_seq' does not exist error.
If i try to replace the nextval statement with:
select nextval('act_individual_id_seq') AS v_nextSeq ;
It will not compile. The commented out statement will not compile either.
I would appreciate it if anyone could point me in the right direction.
November 26th, 2012, 04:18 PM
I have sorted the problem. I should have used INTO instead of AS in the first statement, i.e.
select nextval(''act_individual_id_seq'') INTO v_nextSeq ;
Thanks for your help