Results 1 to 2 of 2
  1. #1
    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

  2. #2
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •