Here are the steps I am going through to port some code from Oracle PL/SQL to PostgreSQL PLPGSQL.
Here is the first line in the Oracle version
create or replace procedure stats_rollup1_proc is
This becomes
create or replace function stats_rollup1_proc() returns int as $$
DECLARE
Now Postgres is not my fulltime focus, just a part of the overall job. a PG expert could probably do it better.
The things to note here:
- procedure is not a keyword in plpgsql. Thus function and returns. I suspect I could return a void type, but haven’t looked that hard.
- Postgres requires the text of a stored procedure to be qutoed. The $$ is a nice way to deal with the requirement.
- DECLARE is optional in Oracle, but required in postgreSQL
At the end of the function:
end stats_rollup1_proc;
Becomes
return 0;
end /*stats_rollup1_proc;*/
$$ LANGUAGE plpgsql
I like leaving the comment in there to match the original begin, since the functions get long enough that it is hard to track. There is no harm in returning 0, even if we don’t really use it as a return code. The final $$ closes out the one from the start of the function. We have to specify the language used, as this same mechanism can be used for any of the languages embedded inside PostgreSQL. Yes, even Python.
Ok, for some mechanics.
In the Declare section of the oracle code we have:
cursor time_cur(current_time_in date) IS
select distinct sample_time
from VPX_SAMPLE_TIME1
WHERE ROLLUP_COUNTER IS NULL
AND SAMPLE_TIME < current_time_in-1/24
order by 1 asc;
v_time VPX_SAMPLE_TIME1.sample_time%type;
which is later used like this:
open time_cur(v_rollup_start_time);
loop
fetch time_cur into v_time;
exit when time_cur%notfound;
In PostgreSQL these can be inlined like this:
for v_time in
select distinct sample_time
from VPX_SAMPLE_TIME1
WHERE ROLLUP_COUNTER IS NULL
AND SAMPLE_TIME < current_time_in-1/24
order by 1 asc
loop
Although I have not yet figured out how to handle the notfound.
