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.