On 8/31/16 12:38 AM, Jaime Casanova wrote: > 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?
That is the syntax that Oracle uses. We could make up our own. > 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 The PL/pgSQL part doesn't work well yet. If you want to play around, use the PL/Python integration. -- Peter Eisentraut http://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
