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)

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate