Correct syntax to run a command to create a Procedure or Function in a SQL Job connected to a Postgresql database -

book

Article ID: 211575

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine

Issue/Introduction

If this command is executed in an SQL job to create a function on remote PostgreSQL database:

==============================================

create or replace function fct_test() returns void as $$
Begin
  select 1 from ah;
end
$$ LANGUAGE plpgsql;

==============================================

The task aborts with ENDED_NOT_OK.

In the PLOG of the jobs is  the following content:

==============================================

2021-03-29 14:25:29 - U02000005 Job 'JOBS.SQLOBP' with RunID 'XXXXX' started.
2021-03-29 14:25:29 - U02012001 Successfully established connection to 'muntest000674.bpc.broadcom.net:5434/AE' with user 'AUTOMIC'.
2021-03-29 14:25:29             PostgreSQL
2021-03-29 14:25:29             10.4
2021-03-29 14:25:29 - U02012014 JDBC Driver version: 42.2.1
2021-03-29 14:25:29             create or replace function fct_test() returns void as $$
2021-03-29 14:25:29             Begin
2021-03-29 14:25:29               select 1 from ah
2021-03-29 14:25:29             end
2021-03-29 14:25:29             $$ LANGUAGE plpgsql
2021-03-29 14:25:29             ERROR: unexpected end of function definition at end of input
  Position: 90
2021-03-29 14:25:29 - U02004025 Job-script execution was aborted.
2021-03-29 14:25:29 - U02012006 SQL Rollback executed

==============================================

 

Cause

This is normal as no matter what DB vendor is used by default every statement is on autocommit for SQL jobs and therefore ';/$$' will not work...
change the job to:

=============================================

SQL_SET_STATEMENT_TERMINATOR [email protected];

create or replace function fct_test() returns void as $$

Begin

select 1 from ah;

end

$$ LANGUAGE plpgsql;

=============================================

The execution of this job will return the following PLOG:

=============================================

2021-03-22 15:03:50 - U02012014 JDBC Driver version: 42.2.8
2021-03-22 15:03:50             create or replace function fssize_test() returns void as $$
2021-03-22 15:03:50             Begin
2021-03-22 15:03:50              select 1 from ah;
2021-03-22 15:03:50             end
2021-03-22 15:03:50             $$ LANGUAGE plpgsql;
2021-03-22 15:03:50 - U02012003 0 row(s) affected
2021-03-22 15:03:50 - U02004026 Job script ended normally.

=============================================

The tasks will run successfully and the function fct_test() will be created as requested on the target Database.

Environment

Release : 12.3  Component : AUTOMATION ENGINE

Resolution