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;
:new.conf_id := CONF_ID_SEQ.NEXTVAL;
is slightly more efficient that the outdatedselect from dual
– a_horse_with_no_name Feb 13 at 13:02