Declare a variable inside a transaction pl/pgsql (not a function nor a SP)

Question

I'm injecting large data into a PostgreSQL database, and while I am writing the SQL script, I want to be able to rollback my data's insertion to ease my tests.

I succeed to declare a variable and use it :

do $$
declare 
    id_accor integer := NULL;
begin
    select "ID_TRANSCODIFICATION_REFERENTIEL" 
    from public.transcodifications_referentiels 
    into id_accor
    where "DENOMINATION" = 'Accor';
    
    raise notice 'id_accor %', id_accor;

end; $$

I succeed to use a transaction :

begin transaction;
    
    INSERT INTO public.transcodifications_referentiels
    ("DENOMINATION", "EST_OBLIGATOIRE", "EST_ACTIVE", "SQL_ID_MASTER", "SQL_ID_USER", "SQL_IP_USER", "SQL_LOCKED", "SQL_INSERTED", "SQL_UPDATED", "SQL_EXE", "SQL_PROCEDURE")
    VALUES('Accor', true, true, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL);
    
    select "ID_TRANSCODIFICATION_REFERENTIEL" 
    from public.transcodifications_referentiels
    where "DENOMINATION" = 'Accor';
    
rollback transaction;

But I can't do both : declare a variable inside a transaction which will be rolled back.

DECLARE
  id_accor integer;
BEGIN;
  INSERT INTO public.transcodifications_referentiels
    ("DENOMINATION", "EST_OBLIGATOIRE", "EST_ACTIVE", "SQL_ID_MASTER", "SQL_ID_USER", "SQL_IP_USER", "SQL_LOCKED", "SQL_INSERTED", "SQL_UPDATED", "SQL_EXE", "SQL_PROCEDURE")
    VALUES('Accor', true, true, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL);

  rollback;
END;

-- output : syntaxe error near "integer"

begin transaction;
    declare 
    id_accor integer := NULL;
begin
    INSERT INTO public.transcodifications_referentiels
    ("DENOMINATION", "EST_OBLIGATOIRE", "EST_ACTIVE", "SQL_ID_MASTER", "SQL_ID_USER", "SQL_IP_USER", "SQL_LOCKED", "SQL_INSERTED", "SQL_UPDATED", "SQL_EXE", "SQL_PROCEDURE")
    VALUES('Accor', true, true, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL);
    
    select "ID_TRANSCODIFICATION_REFERENTIEL" 
    from public.transcodifications_referentiels
    where "DENOMINATION" = 'Accor';
end;
rollback transaction;

-- output : syntaxe error near "integer"

I've tried so many syntaxes I can't copied all of them in this post. I've read all about the DO statement, and I try to use an BEGIN/Exception block even if it is not appropriate for my goal.

I'm used to T-sql, and I've done those kind of scripts a lot in this language, but PL/PG is new to me.

[This](https://stackoverflow.com/questions/42638907/savepoint-in-postgresql-function) SO thread may help you. — Stefanov.sm, Oct 25 '22 at 11:01

Zegarek · Answer 1 · 2022-10-25T12:07:43.070

You can use an anonymous PL/pgSQL block after starting a transaction, then roll it back, combining your first and second example.

create table test as select 1 col1;
--SELECT 1
commit;
--COMMIT
table test;
-- col1
--------
--    1
--(1 row)
begin transaction;
--BEGIN
do $$
declare
    some_test_variable integer:=2;
begin
    raise notice 'Hello from anonymous PL/pgSQL block inside a transaction.';
    insert into test select some_test_variable; 
end;
$$;
--NOTICE:  Hello from an anonymous PL/pgSQL block inside a transaction.
--DO
table test;
-- col1
--------
--    1
--    2
--(2 rows)
rollback transaction;
--ROLLBACK
table test;
-- col1
--------
--    1
--(1 row)

Declare a variable inside a transaction pl/pgsql (not a function nor a SP)

1 Answers1