I had assumed after I had created a working SQL Script I would just be able to wrap the whole thing easily into a function and then bang it would be off to the races.
My script really needed to be run in order and for some as yet undefined reason I was getting particular errors where a table would be created and then a following query would add or alter that table. It looked like the second query was trying to adapt the table prior to its creation with an inevitable error.
I managed to get it working by making each SQL Query a function and then scripting the functions consecutively in a separate function using the PERFORM instruction.
I incorporate into this the check_function_bodies switch which just allows the creation of sql referring to objects that may not be in existence yet.
BEGIN; SET LOCAL check_function_bodies TO FALSE; CREATE OR REPLACE FUNCTION query01() returns void as $$ CREATE TABLE t001start ( pkid serial primary key, geompkidt001 geometry(point,27700) ); $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION query02() returns void as $$ CREATE TABLE t002end ( pkid serial primary key, geompkidt002 geometry(point,27700) ); $$ LANGUAGE SQL; COMMIT;
And then subsequently I create a function that runs the functions.
CREATE OR REPLACE FUNCTION runallthequeries() returns text as $BODY$ BEGIN PERFORM query01(); PERFORM query02(); RETURN 'process end'; END; $BODY$ LANGUAGE plpgsql;