How to Call stored procedure function in airflow using PostgresOperator

Question

I am not sure about how to call stored procedure in airflow operator- eg. Stored Procedure is- Call goal.dba.sp_ctrl_calendar()

My code-

from airflow.operators.postgres_operator import PostgresOperator
sp_ctrl_calendar = PostgresOperator(
        task_id = 'sp_ctrl_calendar',
        sql = 'goal.dba.sp_ctrl_calendar(); end;',
        postgres_conn_id = 'redshift',
        autocommit = True)

Is this approach correct?

Answer 1

in postgresql ( or redshift) to call a proc , command is call procname so your sql command shoulld look like this:

from airflow.operators.postgres_operator import PostgresOperator
sp_ctrl_calendar = PostgresOperator(
        task_id = 'sp_ctrl_calendar',
        sql = 'call goal.dba.sp_ctrl_calendar',
        postgres_conn_id = 'redshift',
        autocommit = True)

Answer 2

The correct answer is-

sql_command_1 = 'call goal.dba.sp_ctrl_calendar()'
sp_ctrl_calendar = PostgresOperator(
    task_id = 'sp_ctrl_calendar',
    sql = sql_command_1,
    postgres_conn_id = 'redshift',
    autocommit = True)