Multiple Begins in a PostgresSQL script-CodePudding

I currently am trying to have a single sql script. I could achieve my end result by executing all scripts one after the other, sequentially.

Currently my scripts creates a bunch of tables and stored procedures, all wrapped within a transaction/Begin and End.

DO $$
BEGIN

    CREATE TABLE IF NOT EXISTS licenses (
        license_id smallint NOT NULL,
        name varchar(25) NOT NULL,
        PRIMARY KEY (license_id)
    );
    ..... create lots of tables ....

END$$;

Now I'm trying to add to this script a couple stored procedures, it fails because the stored procedure contains BEGIN and that doesn't work within an existing transaction.

I could put the sproc underneath the first transaction but the new sproc should only execute when the previous transaction of creating tables have been committed. May I have any suggestions to move forward.

CodePudding user response：

The BEGIN you are using is not the SQL command BEGIN but a PL/pgSQL block structure

To start a transaction, stick to SQL:

begin transaction;

CREATE TABLE IF NOT EXISTS licenses (
   license_id smallint NOT NULL,
   name varchar(25) NOT NULL,
   PRIMARY KEY (license_id)
);

..... create lots of tables ....

create procedure foo() 
as 
$body$
begin 
  ...
end;
$body$
language plpgsql;

create table ...;

commit;

begin transaction;
... create other tables and procedures
commit;