CODEWITHSUNDEEP
postgresqlfunctiontriggersinsert
Bach-Nga
Bach-Nga

Reputation: 45

how to run function insert or update on conflict on a table for PostgreSQL

I want to insert and update one table from another using the on conflict do update-feature.

I have tried to execute the function and none of them work.

create or replace FUNCTION ecisdrdm.pr_mig_stg_application_cdim (v_Ret OUT bigint ) as $$ declare v_ErrorCode bigint; v_ErrorMsg varchar(512); v_Module varchar(32) = 'pr_mig_stg_application_cdim';

begin

-- MERGING: STG_APPLICATION_CDIM into APPLICATION_CDIM

INSERT INTO application_cdim AS prod (prod.application_id, prod.receipt_number,prod.init_frm_id, prod.frm_typ_id,
prod.sbmtd_dt_id, prod.crtd_user_id, prod.init_src_sys_id, prod.init_svc_ctr_id, prod.mig_filename) SELECT stg.application_id, stg.receipt_number, stg.init_frm_id, stg.frm_typ_id, stg.sbmtd_dt_id, stg.crtd_user_id, stg.init_src_sys_id, stg.init_svc_ctr_id, stg.mig_filename FROM ecisdrdm.stg_application stg ON CONFLICT (application_id) DO UPDATE SET ( prod.init_frm_id, prod.frm_typ_id,prod. sbmtd_dt_id, prod.crtd_user_id, prod.init_src_sys_id, prod.init_svc_ctr_id, prod.mig_filename, prod.mig_modified_dt ) = (SELECT stg.init_frm_id, stg.frm_typ_id, stg.sbmtd_dt_id, stg.crtd_user_id, stg.init_src_sys_id, stg.init_svc_ctr_id, stg.mig_filename, current_timestamp FROM ecisdrdm.stg_application_cdim stg WHERE prod.receipt_number = stg.receipt_number );
RETURN;

---- -- Set the return code to 0 ----

v_Ret := SQLCODE;

-- Exception error handler ---- exception when others then v_ErrorCode := SQLCODE; v_ErrorMsg := SQLERRM; v_Ret := v_ErrorCode;

---- -- Commit the record into the ErrorLog ---- PERFORM pr_write_error_log( CURRENT_USER, inet_server_addr(), v_Module, v_ErrorCode, v_ErrorMsg );

---- -- Intentionally leaving the "commit" to application ---- end; $$ language plpgsql;

ERROR: function result type must be specified SQL state: 42P13 SELECT ecisdrdm.pr_mig_stg_application_cdim(2000); ERROR: function ecisdrdm.pr_mig_stg_application_cdim(integer) does not exist LINE 1: SELECT ecisdrdm.pr_mig_stg_application_cdim(2000); ^

HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 8

ERROR: function pr_mig_stg_application_cdim() does not exist LINE 1: SELECT pr_mig_stg_application_cdim (); ^

HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 8

Any suggestions on what I'm doing wrong? and how to I run the function as same as Oracle? DB version 11.1 runs in AWS with pgAdmin v.4.4

Upvotes: 0

Views: 185

Answers (1)

Bach-Nga
Bach-Nga

Reputation: 45

working script

    CREATE OR REPLACE FUNCTION cidrstaging.pr_mig_stg_application_cdim(
    OUT v_ret text)
    RETURNS text
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
DECLARE 
    v_module                text = 'pr_mig_stg_application_cdim';
    host                    text = inet_server_addr();
    errorcode               text;
    errormsg                text;
    errormsg_detail         text;
    errormsg_hint           text;
        
BEGIN

    ---
    -- Simply delete the data from production table
    ----
    
    DELETE FROM cidrdata.ref_application_cdim WHERE 1=1;

    ---
    --  refresh ref_action_code from stg_ref_action_code
    ---

    INSERT INTO cidrdata.ref_application_cdim
    SELECT stg.code, stg.action, stg.mig_filename
    FROM cidrstaging.stg_ref_application_cdim stg;

    ----
    --   Set return to "Success" for pr_mig_stg_application_cdim function
    ----
    v_ret := 'Success';  

EXCEPTION
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS 
        errormsg = MESSAGE_TEXT,
        errormsg_detail = PG_EXCEPTION_DETAIL,
        errormsg_hint = PG_EXCEPTION_HINT;  
        errorcode := SQLSTATE;
        v_ret := concat('ERROR - FUNC: ' || v_module || ' ERRORCODE: ', errorcode, ' MSG: ' || errormsg || ' ', errormsg_detail || ' ', errormsg_hint);

    -- NOTE: Only writes to errorlog table if function is called directly
    -- If called through the pr_mig_stg_application_cdim function the exception is raised and caught there instead
    PERFORM cidrstaging.pr_write_error_log( CURRENT_USER, host, v_module, errorcode, v_ret );

END;
$BODY$;

Upvotes: 0

Related Questions