Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2009
    Posts
    4

    Unanswered: ERROR: cursor "<unnamed portal 11>" does not exist

    Hi All,
    I want to increment a column value by 1. I create a procedure. Here is the code.

    CREATE OR REPLACE FUNCTION GET_NEXT_VALUE_OF(VARCHAR)
    RETURNS NUMERIC AS
    $BODY$

    DECLARE
    V_COUNTER NUMERIC;
    NEXT_VAL_CUR refcursor;
    RETURN_VALUE NUMERIC;

    BEGIN
    V_COUNTER := RETURN_VALUE;
    OPEN NEXT_VAL_CUR FOR select "SEQ_VALUE" FROM "IPG_SEQ_NEXT_VALUE" WHERE "SEQUENCE_NAME" = 'IPG_MERCHANT_AUDIT_SEQ' FOR UPDATE;

    LOOP
    FETCH NEXT_VAL_CUR INTO V_COUNTER;

    IF NOT FOUND THEN

    SELECT -1 AS RESULT;
    CLOSE NEXT_VAL_CUR;
    --RETURN_VALUE := -1;

    ELSE

    UPDATE "IPG_SEQ_NEXT_VALUE" SET
    --"SEQ_VALUE" = RETURN_VALUE + 1,
    "SEQ_VALUE" = V_COUNTER + 1,
    "MODIFIED_BY"='Procedure',
    "MODIFIED_DATE"= '2009-05-11 00:00:00'
    WHERE "SEQUENCE_NAME" = 'IPG_MERCHANT_AUDIT_SEQ';
    CLOSE NEXT_VAL_CUR;

    END IF;
    END LOOP;
    END;

    $BODY$

    LANGUAGE 'plpgsql' VOLATILE
    COST 100;

    ALTER FUNCTION GET_NEXT_VALUE_OF(VARCHAR) OWNER TO postgres;

    After that I run a insert query. But it was giving this error message.

    ERROR: cursor "<unnamed portal 11>" does not exist
    CONTEXT: PL/pgSQL function "get_next_value_of" line 12 at FETCH
    PL/pgSQL function "trg_ipg_bank_account_function" line 7 at assignment

    ********** Error **********

    ERROR: cursor "<unnamed portal 11>" does not exist
    SQL state: 34000
    Context: PL/pgSQL function "get_next_value_of" line 12 at FETCH
    PL/pgSQL function "trg_ipg_bank_account_function" line 7 at assignment
    I can't find any solutions for this. Can anyone help me.
    Thanks.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    What are you trying to accomplish here?

    Why not just define the default value for the column in question as the next value from the sequence ? (shortcut - set the column type to SERIAL when creating the database)

    You're incrementing all the EXISTING values in the column by one where ? Why? (is this some sort of EAV approach?) Or, you've deleted a record, and don't want a 'gap' in the numbering?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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