I'm trying to learn PostgreSQL stored procedures. Specifically creating the procedure below in PSQL.
CREATE OR REPLACE PROCEDURE BUILD_AND_POPULATE(INOUT cresults refcursor) LANGUAGE PLPGSQL AS $$ BEGIN BEGIN; -- I've tried removing this but the behaviour is the same cresults:= 'cur'; DROP TABLE IF EXISTS procsampledata; CREATE TABLE procsampledata as select x,1 as c2,2 as c3, md5(random()::text) from generate_series(1,10) x; COMMIT; OPEN cresults FOR SELECT * FROM procsampledata; END; $$;
Then I execute it like so, but receive an error:
postgres=# call build_and_populate(null); ERROR: invalid transaction termination CONTEXT: PL/pgSQL function build_and_populate(refcursor) line 6 at COMMIT
I've tried with AUTOCOMMIT set to both on and off.
This is my Postgres version
PostgreSQL 11.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit
Can anyone see what I'm doing wrong? Thank you!