In the last post we’ve learned the basics for migrating a sample procedure from PL/SQL to PL/pgSQL. In this post we’ll actually start with migrating the code. The first step in the original procedure was to iterate over the foreign key constraints of the user’s tables and to disable them:

  for i in ( select table_name
                  , constraint_name
               from user_constraints
              where constraint_type ='R'
                and status = 'ENABLED' )
  loop
    execute immediate 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name;
  end loop;
  dbms_output.put_line ( 'Disabled foreign key constraints');

If you try this as it is in PL/pgSQL this will fail for several reasons:

postgres=> \! cat a.sql
create or replace procedure p1() as
$$
declare
begin 
  for i in ( select table_name
                  , constraint_name
               from user_constraints
              where constraint_type ='R'
                and status = 'ENABLED' )
  loop
    execute immediate 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name;
  end loop;
  dbms_output.put_line ( 'Disabled foreign key constraints');

end;
$$ language plpgsql;
postgres=> \i a.sql
psql:a.sql:16: ERROR:  loop variable of loop over rows must be a record variable or list of scalar variables
LINE 5:   for i in ( select table_name

The error message is pretty clear, we need to use a variable of type “record” if we want to iterate over a number of rows, so:

postgres=> \! cat a.sql
create or replace procedure p1() as
$$
declare
  i record;
begin 
  for i in ( select table_name
                  , constraint_name
               from user_constraints
              where constraint_type ='R'
                and status = 'ENABLED' )
  loop
    execute immediate 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name;
  end loop;
  dbms_output.put_line ( 'Disabled foreign key constraints');

end;
$$ language plpgsql;
postgres=> \i a.sql
psql:a.sql:17: ERROR:  syntax error at or near "dbms_output"
LINE 14:   dbms_output.put_line ( 'Disabled foreign key constraints'...

dbms_output is an Oracle package, this does not exist in PostgreSQL. We have two options:

  • Create a schema called “dbms_output” and in there create a procedure “put_line” which does what we want
  • Use what PL/pgSQL provides by default, and this is RAISE

(There is a third option, which is orafce, but we’re not going to look into this one)

postgres=> \! cat a.sql
create or replace procedure p1() as
$$
declare
  i record;
begin 
  for i in ( select table_name
                  , constraint_name
               from user_constraints
              where constraint_type ='R'
                and status = 'ENABLED' )
  loop
    execute immediate 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name;
  end loop;
  raise notice 'Disabled foreign key constraints';

end;
$$ language plpgsql;
postgres=> \i a.sql
CREATE PROCEDURE

Now the procedure can be created, but there are more issues to fix. If we execute the procedure we get this:

postgres=> call p1();
ERROR:  relation "user_constraints" does not exist
LINE 3:                from user_constraints
                            ^
QUERY:  ( select table_name
                  , constraint_name
               from user_constraints
              where constraint_type ='R'
                and status = 'ENABLED' )
CONTEXT:  PL/pgSQL function p1() line 5 at FOR over SELECT rows

“user_constraints” is an Oracle catalog view, and this view is of course not existing in PostgreSQL. But PostgreSQL provides the catalog table pg_constraint, so:

postgres=> \! cat a.sql
create or replace procedure p1() as
$$
declare
  i record;
begin 
  for i in ( select conrelid::regclass as table_name
                  , conname as constraint_name
               from pg_constraint
              where contype ='f' )
  loop
    execute immediate 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name;
  end loop;
  raise notice 'Disabled foreign key constraints';

end;
$$ language plpgsql;
postgres=> \i a.sql
CREATE PROCEDURE
postgres=> call p1();
ERROR:  type "immediate" does not exist
LINE 1: immediate 'alter table ' || i.table_name || ' disable constr...
        ^
QUERY:  immediate 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name
CONTEXT:  PL/pgSQL function p1() line 10 at EXECUTE

Next issue: “IMMEDIATE” does not exist in PL/pgSQL, but this is easy to fix, just remove it:

postgres=> \! cat a.sql
create or replace procedure p1() as
$$
declare
  i record;
begin 
  for i in ( select conrelid::regclass as table_name
                  , conname as constraint_name
               from pg_constraint
              where contype ='f' )
  loop
    execute 'alter table ' || i.table_name || ' disable constraint ' || i.constraint_name;
  end loop;
  raise notice 'Disabled foreign key constraints';

end;
$$ language plpgsql;
postgres=> \i a.sql
CREATE PROCEDURE
postgres=> call p1();
ERROR:  syntax error at or near "constraint"
LINE 1: alter table t2 disable constraint t2_b_fkey
                               ^
QUERY:  alter table t2 disable constraint t2_b_fkey
CONTEXT:  PL/pgSQL function p1() line 10 at EXECUTE

In PostgreSQL you cannot disable constraints as you can do it in Oracle:

postgres=# alter table t2 disable 
ROW LEVEL SECURITY  RULE                TRIGGER

The way to do this in PostgreSQL is to drop the constraints and re-create them afterwards but for this we need to store the “create” commands. One way of doing that would be something like this:

create or replace procedure p1() as
$$
declare
  i record;
  constraint_create text[];
  tmp text;
  tmp2 text;
  counter int := 1;
begin
  for i in ( select oid
                  , conrelid::regclass as table_name
                  , conname as constraint_name
               from pg_constraint
              where contype ='f' )
  loop
    select 'alter table ' || i.table_name || ' add constraint ' || i.constraint_name || ' ' || pg_get_constraintdef ( i.oid ) into tmp;
    constraint_create[counter] := tmp;
    execute 'alter table ' || i.table_name || ' drop constraint ' || i.constraint_name;
    counter := counter + 1;
  end loop;
  raise notice 'Dropped foreign key constraints';
  -- load data
  -- re-create the foreign keys
  foreach tmp2 in array constraint_create
  loop
    raise notice 'Re-creating foreign key constraint: %', tmp2;
    execute tmp2;
  end loop;
end;
$$ language plpgsql;

This almost completes the migration of the code but there is still the topic of “exceptions”, and that’s for the next post.

Post Views: 28
Thumbnail [60x60]
by
Daniel Westermann