I was migrating an oracle(12c) package to postgres(Enterprise db 10.5) using ora2pg.
The migration run successfully for most of the functions. However, one function referenced multiple times in the package is causing errors.
I am giving both versions of the code below:
oracle
create or replace package body sms_package as
--- Other functions
PROCEDURE LOG_MESSAGE (LogType varchar2, LogDetails varchar2) IS
BEGIN
INSERT INTO SMS_TABLE (REF_DATE, LOG_TYPE, LOG_DETAILS)
VALUES (SYSDATE, LogType, LogDetails);
COMMIT;
END;
ora2pg
CREATE OR REPLACE FUNCTION log_message (LogType text, LogDetails text) RETURNS VOID AS $body$
BEGIN
INSERT INTO SMS_TABLE(REF_DATE, LOG_TYPE, LOG_DETAILS)
VALUES (clock_timestamp(), LogType, LogDetails);
COMMIT;
END;
$body$
LANGUAGE PLPGSQL
;
An example of the references:
oracle
procedure sendtest (dash_type varchar2, sms_message varchar2) IS
cursor x is
SELECT ISDN FROM oracle_store;
begin
dbms_output.put_line('1');
if sms_message is not null then
for i in x loop
insert into s_table(message_text, received_time, DELIVERY_STATUS, retry_attempts) values (..);
sms_package.LOG_MESSAGE (dash_type, 'sending to '||i.ISDN||' message =>'||sms_message);
dbms_output.put_line('notification_type='||dash_type||' sms='||sms_message);
end loop;
commit;
end if;
end;
ora2pg
CREATE OR REPLACE FUNCTION sendtest (dash_type text, sms_message text) RETURNS VOID AS $body$
DECLARE
x CURSOR FOR
SELECT ISDN FROM postgres_store;
BEGIN
RAISE NOTICE '1';
if sms_message is not null then
for i in x loop
insert into s_table(message_text, received_time, DELIVERY_STATUS, retry_attempts) values ();
sms_package.LOG_MESSAGE(dash_type, 'sending to '||i.ISDN||' message =>'||sms_message);
RAISE NOTICE 'notification_type=% sms=%', dash_type, sms_message;
end loop;
commit;
end if;
end;
$body$
LANGUAGE PLPGSQL
There appears to be a problem in calling the function from within another function as am getting this error
[42601] ERROR: syntax error at or near "sms_package"
How can I execute another function from within a function in postgres. How can I call that function from within the another function
Best Answer
Since
sms_package.LOG_MESSAGE
is a function in PostgreSQL, you have to call it usingPERFORM
in PL/pgSQL:But there are other problems in your code. At least the
COMMIT
in the finction definition will cause an error.You may have to redesign and rewrite your code.