Restrictions

  • A trigger function does not support autonomous transactions.

  • In the autonomous transaction block of a function or stored procedure, static SQL statements do not support variable transfer.

    -- Autonomous transactions do not support the execution of the following functions. The SQL statement contains the variable i.
    CREATE OR REPLACE FUNCTION autonomous_easy_2(i int) RETURNS integer
    LANGUAGE plpgsql
    AS $$
    DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
    START TRANSACTION;
    INSERT INTO test1 VALUES (i, 'test');
    COMMIT;
    RETURN 42;
    END;
    $$;
    -- To use the parameter transfer, use the dynamic statement EXECUTE to replace variables. The following is an example:
    CREATE OR REPLACE FUNCTION autonomous_easy(i int) RETURNS integer
    LANGUAGE plpgsql
    AS $$
    DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
    START TRANSACTION;
    EXECUTE 'INSERT INTO test1 VALUES (' || i::integer || ', ''test'')';
    COMMIT;
    RETURN 42;
    END;
    $$;
    
  • Autonomous transactions do not support nesting.

    NOTICE: In a function that contains an autonomous transaction, it is not allowed to explicitly execute another function or stored procedure that contains an autonomous transaction through PERFORM, SELECT, or CALL. However, another function or stored procedure that contains an autonomous transaction can be explicitly called in the last RETURN.

  • A function containing an autonomous transaction does not support the return value of parameter transfer.

    -- In the following example, the return value ret is not transferred and only null is returned.
    create or replace function at_test2(i int) returns text
    LANGUAGE plpgsql
    as $$
    declare
    ret text;
    pragma autonomous_transaction;
    begin
    START TRANSACTION;
    insert into at_tb2 values(1, 'before s1');
    if i > 10 then
    rollback;
    else
    commit;
    end if;
    select val into ret from at_tb2 where id=1;
    return ret;
    end;
    $$;
    
  • A stored procedure or function that contains an autonomous transaction does not support exception handling.

  • A trigger function does not support autonomous transactions.

Feedback
编组 3备份
    openGauss 2024-05-24 00:44:50
    cancel