Thread: Transaction in the funtions
Hello all !! Can anyone help me please ?? I would like to get the execution of each the statement in the LOOP ( in the function change_owner() below) each in its own transaction. the construction like the folowing errors out with ERROR: syntax error at or near "LOOP" LOOP BEGIN; PERFORM change_schema_owner( sch_rec.nspname ); COMMIT; END LOOP; getting same errors when tried to start the transaction into the function change_schema_owner() tried something similar to BEGIN BEGIN; EXECUTE 'ALTER SCHEMA ' ||sch_name || ' OWNER TO new_role' ; COMMIT; RETURN ; END; called SET TRANSACTION ISOLATION LEVEL SERIALIZABLE before the calls .. but looks like all the LOOP calls are bieng done in one transaction since I am getting an error like : ERROR: out of shared memory HINT: You may need to increase max_locks_per_transaction. CONTEXT: SQL statement "ALTER TABLE "test"."test_ages" OWNER TO "test"" PL/pgSQL function "change_schema_owner" line 24 at execute statement -- functions I am using for that CREATE OR REPLACE FUNCTION owner_migration() .. $BODY$ DECLARE sch_rec RECORD; BEGIN FOR sch_rec IN SELECT * FROM pg_catalog.pg_namespace LEFT JOIN pg_catalog.pg_user ON pg_user.usesysid=pg_namespace.nspowner WHERE pg_user.usename = 'test' LOOP PERFORM change_schema_owner( sch_rec.nspname ); END LOOP; RETURN ; END; CREATE OR REPLACE FUNCTION change_schema_owner(sch_name character varying) ... ... BEGIN -- here I would like to have a transaction and commit it on return EXECUTE 'ALTER SCHEMA ' ||sch_name || ' OWNER TO new_role' ; { statements to change owner for all the objects in the schema } RETURN ; END; ----------------------------------- Vladimir Calmic Systems Analyst OPTIMUM-WEB http://www.optimum-web.com +373 22 571458 +373 79 573313 (mobile)