Recently migrated a database from Oracle to Postgres. I'm working on an ETL job (that worked in the past with Oracle) that calls a procedure (which is referred to as a function in Postgres) using a DBEXECUTE component with no input or output values. When trying to use the DBEXECUTE to call the "function" in am getting errors.
How do you call Postgres functions using the DBEXECUTE component?
Thanks in advanced!
How do you call Postgres functions using the DBEXECUTE component?
Thanks in advanced!
-
Generally speaking, when a user migrates a database that includes so-called user-defined function (function or procedure), he just needs to make sure that he has initialized the function in his new database using the appropriate syntax (Oracle SQL slightly differs from PostgreSQL). However, DBExecute setup should then work without any change. Please refer to CloverETL Designer Documentation (chapter "Calling Stored Procedures and Functions"):
DBExecute Documentation
It means that we define the function in Postgres database (in PostgreSQL language), for example, this way:CREATE OR REPLACE
FUNCTION delete_function()
RETURNS void AS'
DELETE FROM data
WHERE num=70;
' LANGUAGE SQL;
And then we can call it from CloverETL by executing this:{call delete_function()};
In your case, I believe, the first step has been already done (most likely outside CloverETL Designer, but it can be executed in DBExecute as well), therefore you can simply call the function using {call delete_function()};
In other words, even though SQL syntax of the function varies in different databases, DBExecute setup of calling the function remains the same. (Even though PostgreSQL usually uses SELECT for calling function).
Best Regards, -
Thank you. This is the answer. I didn't see any syntax for functions without any input parameters in the documentation. The issue was I was missing the '()' at the end of the function name, which was not needed when the job was connected to the Oracle database.
I will also note for future readers that I do have the "call as stored procedure" attribute selected.
Please sign in to leave a comment.
Comments 2