On 30 August 2016 at 20:50, Peter Eisentraut <peter.eisentr...@2ndquadrant.com> wrote: > > - Patches to PL/pgSQL to implement Oracle-style autonomous transaction > blocks: > > AS $$ > DECLARE > PRAGMA AUTONOMOUS_TRANSACTION; > BEGIN > FOR i IN 0..9 LOOP > START TRANSACTION; > INSERT INTO test1 VALUES (i); > IF i % 2 = 0 THEN > COMMIT; > ELSE > ROLLBACK; > END IF; > END LOOP; > > RETURN 42; > END; > $$; >
this is the syntax it will use? i just compiled this in head and created a function based on this one. The main difference is that the column in test1 it's a pk so i used INSERT ON CONFLICT DO NOTHING and i'm getting this error postgres=# select foo(); LOG: namespace item variable itemno 1, name val CONTEXT: PL/pgSQL function foo() line 7 at SQL statement STATEMENT: select foo(); ERROR: null value in column "i" violates not-null constraint DETAIL: Failing row contains (null). STATEMENT: INSERT INTO test1 VALUES (val) ON CONFLICT DO NOTHING ERROR: null value in column "i" violates not-null constraint DETAIL: Failing row contains (null). CONTEXT: PL/pgSQL function foo() line 7 at SQL statement STATEMENT: select foo(); ERROR: null value in column "i" violates not-null constraint DETAIL: Failing row contains (null). CONTEXT: PL/pgSQL function foo() line 7 at SQL statement this happens even everytime i use the PRAGMA even if no START TRANSACTION, COMMIT or ROLLBACK are used -- Jaime Casanova www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
