If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > PostgreSQL > Unable to retrieve a sequence into a variable within a function

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 2
Unable to retrieve a sequence into a variable within a function

Hi,

I am new to POSTGRES and am having trouble in retrieving a sequence next value into a declared variable within a function. My 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
minvalue 1
maxvalue 99999999
start with 300
increment by 1 ;

When I use the following statement directly it returns 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
'DECLARE
v_nextSeq int ;
BEGIN
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,
middle_name, surname,
known_name, home_tel,
office_tel, mobile, email,
logon_name, password,
active, datim)
values (v_nextSeq, 2, $1, $2, $3, $4, $5, $9,
$10, $11, $12, $13, $14, true,
current_date) ;

Insert Into act_staff( id, staff_no, unit_id,
dept_id, active)
values ( v_nextSeq, $6, $7, $8, true) ;
END;'
LANGUAGE plpgsql;

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.
Thanks
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 2
Problem sorted..

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 the help.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On