0

I am using savepoint and rollback in a my below pg script. Getting below error.

ERROR: unsupported transaction command in PL/pgSQL

Please help me how to achieve this.

do
$block$ 
declare
BEGIN
 perform updatecustdtls (
                  custdes               => 'initial state',         
                  custno            => 'C1122',
                  cmd       =>  TO_TIMESTAMP('11/21/2005','dd/mm/yyyy')::TIMESTAMP(0),
                  );   


 savepoint updatecust;                         

update custd set custid = 9 where custno = 'C1122';


    perform updatecustdtls (
                  custdes               => 'middle state',         
                  custno            => 'C4455',
                  cmd       =>  TO_TIMESTAMP('11/11/2006','dd/mm/yyyy')::TIMESTAMP(0),
                  );

rollback to updatecust;  

    perform updatecustdtls (
                  custdes               => 'final state',         
                  custno            => 'C88809',
                  cmd       =>  TO_TIMESTAMP('08/15/2007','dd/mm/yyyy')::TIMESTAMP(0),
                  );

end $block$;

Thanks in advance.

Ram
  • 11
  • 5
  • https://dba.stackexchange.com/questions/145778/does-a-commit-work-within-an-anonymous-plgpsql-function-in-postgresql-9-5 – 404 Nov 06 '19 at 13:42
  • 1
    Possible duplicate of [PostgreSQL cannot begin/end transactions in PL/pgSQL](https://stackoverflow.com/questions/14550241/postgresql-cannot-begin-end-transactions-in-pl-pgsql) – 404 Nov 06 '19 at 13:43
  • Just drop the plpgsql wrapper (and replace `perform` with `select`). You don't need any PL/pgsql here. – Bergi Nov 07 '19 at 01:23
  • could you please help me how to achieve my scenario. Savepoint and rollback. – Ram Nov 07 '19 at 07:21

0 Answers0