PostgreSQL – Migrate Triggers, Functions, and Procedures from Oracle

migrationoracleplpgsqlpostgresql

We run a Java based application which uses Oracle as its backend. While they are rather simple and don't overly use Oracle specific functions, we have lots of triggers, function and procedures written in pl/sql.

Now we are forced to run the same application with a PostgreSQL backend too, this means we will run two instances of our code, one connecting to Oracle and the other to PostgreSQL.
We know that we will need to make some adjustments, but hope to keep them at a minimum.

Is it possible to transfer most of our triggers, functions and procedures to PostgreSQL ? I have had a look at the migration guide and heard rumors that PostgreSQL only supports C as a language for writing triggers and so forth.

To give you an idea of which complexity I am talking about:

CREATE OR REPLACE FUNCTION "STAGE"."NEST"
(X IN number, Y IN number, Z IN NUMBER)
RETURN number

AS

NL number;
BEGIN
  select count(*) into NL from ABC where foo_id = X
        and Y < A and Z > B order by Z;
  RETURN NL;
END;

And a trigger:

CREATE OR REPLACE TRIGGER "STAGE"."SOMETHING" 
  BEFORE INSERT
  on  "CONFIG"
  for each row
begin
   SELECT CONF_ID_SEQ.NEXTVAL INTO :new.CONF_ID FROM DUAL;
END;

Best Answer

You can certainly write triggers, functions and stored procedures (since Postgres 11) in languages other than C - the most popular is probably PL/pgSQL which is similar to Oracle's PL/SQL.

You can also write functions in SQL which is more efficient for simply "query encapsulation".

I would implement the function in your question as a simple SQL function:

CREATE OR REPLACE FUNCTION stage.nest(x integer, y integer, z integer)
RETURNS bigint
AS
$$
  select count(*) 
   from ABC 
   where foo_id = X
     and Y < A 
     and Z > B;
$$
language SQL;

I remove the order by from that statement as it is useless for a query that returns a count(*)

The same function implemented with PL/pgSQL would be:

CREATE OR REPLACE FUNCTION stage.nest(x integer, y integer, z integer)
RETURNS bigint
AS
$$
declare
  l_count bigint;
begin

  select count(*) 
    into l_count
  from ABC 
  where foo_id = X
    and Y < A 
    and Z > B;

  return l_count;
end;
$$
language SQL;

But in general PL/pgSQL should only be used if you do need procedural logic (loops, IF statements etc). Otherwise language sql is more efficient.

You don't really need a trigger in Postgres automatically generate ID values from a sequence. Just define the column as serial and things will be taken care of. If you want to force the use of a sequence, define the column as integer generated always as identity

Nevertheless the trigger would be very similar in Postgres, with the biggest difference that Postgres uses a trigger function that is referenced when defining the trigger. 

create function f_config_trg()
  returns trigger
as
$$
begin
  new.CONF_ID := nextval('confi_id_seq');
end;
$$
language plpgsql;

create trigger config_trigger
  before insert on config
  for each row
  execute procedure f_config_trg();

You can find much more details on PL/pgSQL, functions and trigger functions in the manual

Unrelated to your question: don't get into the habit of using quoted identifiers in Postgres (or Oracle for that matter). They are much more trouble in the long run than they are worth it.

Related Question