Agfdhyk

Please help me to resolve this function error. The cursor explicitly declare %rowtype in PostgreSQL. I was told to convert cursors to pass records around from Oracle to Postgres. Can this be implemented the same way in Postgres? I saw some syntax in the Postgres docs showed a function being created a row variable can be declared to have the same type as the rows of an existing table unfortunately, mine was join tables.

CREATE OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_I864" (v_Ret OUT int ) RETURNS integer as $$



 declare 

   c1 cursor is

      SELECT stg.Service_Center, stg.Receipt_Number, stg.Date_Entered, stg.Sequence_Number,

         stg.SSN, stg.A_Number, stg.Last_Name, stg.First_Name, stg.Middle_Name, stg.DOB,

         stg.Street_Mail, stg.City_Mail, stg.State_Mail, stg.Zip_Mail, stg.Province_Mail,

         stg.Postal_Code_Mail, stg.Country_Mail, stg.Street_Res, stg.City_Res, stg.State_Res,

         stg.Zip_Res, stg.Province_Res, stg.Postal_Code_Res, stg.Country_Res, stg.US_Citizen,

         stg.Basis, stg.Accompany, stg.mig_filename,stg.mig_insert_dt,stg.mig_modified_dt,

         prod.receipt_number as prod_receipt_number, prod.date_entered as prod_date_entered,

         prod.sequence_number as prod_sequence_number

      FROM cidr_staging.stg_i864 stg LEFT OUTER JOIN cidrdba.sc_i864 prod

         ON coalesce(stg.receipt_number,'NULL') = coalesce(prod.receipt_number,'NULL')

        AND coalesce(stg.date_entered,'NULL') = coalesce(prod.date_entered,'NULL')

        AND coalesce(stg.sequence_number,'NULL') = coalesce(prod.sequence_number,'NULL')

      ORDER by stg.mig_seq;



   rec1           c1% rowtype;

   v_rows         int = 0;

   v_seq          int =0;

   v_ErrorCode    int;

   v_ErrorMsg     varchar(512);

   v_Module       varchar(32) = 'PR_MIG_STG_I864';

   v_DDL          varchar(10);

   v_Rec_Num      cidr_staging.stg_i864.receipt_number%type;

   v_Dat_Ent      cidr_staging.stg_i864.date_entered%type;

   v_Seq_Num      cidr_staging.stg_i864.sequence_number%type;



begin



   v_Ret := 0;

   for rec1 in c1



loop

      --dbms_output.put_line('Processing Receipt_Number ' || rec1.receipt_number);

      ----

      -- If the PROD_RECEIPT_NUMBER is null, then the record does not exist in the Production table.

      ----

      if rec1.prod_receipt_number is null then

         v_Rec_Num := rec1.receipt_number;

         v_Dat_Ent := rec1.date_entered;

         v_Seq_Num := rec1.sequence_number;



         insert into cidrdba.sc_i864 values (

            rec1.Service_Center, rec1.Receipt_Number, coalesce(rec1.Date_Entered,''),

            rec1.Sequence_Number, rec1.ssn, rec1.a_number, rec1.last_name, rec1.first_name,

            rec1.middle_name, rec1.dob, rec1.street_mail, rec1.city_mail, rec1.state_mail,

            rec1.zip_mail, rec1.Province_Mail, rec1.Postal_Code_Mail, rec1.Country_Mail,

            rec1.Street_Res, rec1.City_Res, rec1.State_Res, rec1.Zip_Res, rec1.Province_Res,

            rec1.Postal_Code_Res, rec1.Country_Res, rec1.US_Citizen, rec1.Basis, rec1.Accompany,

            rec1.mig_filename, rec1.mig_insert_dt, rec1.mig_modified_dt );

         v_rows := sql%rowcount;

         if v_rows != 1 then

            v_Ret := 1;

            PERFORM pr_write_error_log( sys_context('userenv','session_user'),

                                sys_context('userenv','host'), v_Module,

                                0, 'INSERT processed ' || v_rows );

         end if;

      elsif coalesce(rec1.prod_receipt_number,'NULL')  = coalesce(rec1.receipt_number,'NULL') and

            coalesce(rec1.prod_date_entered,'NULL')    = coalesce(rec1.date_entered,'NULL'

) and

            coalesce(rec1.prod_sequence_number,'NULL') = coalesce(rec1.sequence_number,'NULL') then

         v_Rec_Num := rec1.receipt_number;

         v_Dat_Ent := rec1.date_entered;

         v_Seq_Num := rec1.sequence_number;

         update cidrdba.sc_i864 set

            SSN                    = rec1.SSN,

            A_Number               = rec1.A_Number,

            Last_Name              = rec1.Last_Name,

            First_Name             = rec1.First_Name,

            Middle_Name            = rec1.Middle_Name,

            DOB                    = rec1.DOB,

            Street_Mail            = rec1.Street_Mail,

            City_Mail              = rec1.City_Mail,

            State_Mail             = rec1.State_Mail,

            Zip_Mail               = rec1.Zip_Mail,

            Province_Mail          = rec1.Province_Mail,

            Postal_Code_Mail       = rec1.Postal_Code_Mail,

            Country_Mail           = rec1.Country_Mail,

            Street_Res             = rec1.Street_Res,

            City_Res               = rec1.City_Res,

            State_Res              = rec1.State_Res,

            Zip_Res                = rec1.Zip_Res,

            Province_Res           = rec1.Province_Res,

            Postal_Code_Res        = rec1.Postal_Code_Res,

            Country_Res            = rec1.Country_Res,

            US_Citizen             = rec1.US_Citizen,

            Basis                  = rec1.Basis,

            Accompany              = rec1.Accompany,

            mig_filename           = rec1.mig_filename,

            mig_modified_dt        = current_timestamp

         where

            coalesce(receipt_number,'NULL')         = coalesce(rec1.receipt_number,'NULL')

 and

            coalesce(date_entered,'NULL')           = coalesce(rec1.date_entered,'NULL') and

            coalesce(sequence_number,'NULL')        = coalesce(rec1.sequence_number,'NULL'

);

         v_rows := sql%rowcount;

         if v_rows != 1 then

            v_Ret := 1;

            pr_write_error_log( sys_context('userenv','session_user'),

                                sys_context('userenv','host'), v_Module,

                                0, 'UPDATE processed ' || v_rows || ', expected 1' );

         end if;

      else

         v_Ret := 1;

         PERFORM pr_write_error_log( sys_context('userenv','session_user'),

         sys_context('userenv','host'), v_Module,

         0, 'FAILED to process: ' ||

         rec1.Receipt_Number || ', ' || rec1.Date_Entered || ', ' || rec1.Sequence_Number );

      end if;

      exit when v_Ret != 0 or SQLCODE != 0;

   end loop;



   if v_Ret = 0 then

      v_Ret := SQLCODE;

   end if;



exception

   when others then

      v_ErrorCode := SQLCODE;

      v_ErrorMsg  := SQLERRM;



      if inserting then

         v_ddl := 'INSERT';

      elsif updating then

         v_ddl := 'UPDATE';

      else

         v_ddl := 'UNKNOWN';

      end if;



      PERFORM pr_write_error_log( sys_context('userenv','session_user'),

      sys_context('userenv','host'), v_Module,

      0, v_ddl || ' encountered FALTAL ERROR: (' || v_ErrorCode || '): ' || v_ErrorMsg );



      --dbms_output.put_line('FATAL ERROR while ' || v_ddl || ':  Encountered (' || v_ErrorCode || ') : ' || v_ErrorMsg );

      --dbms_output.put_line('FATAL ERROR:  Record ' || nvl(v_Rec_Num,'null') || '~' || nvl(v_Dat_Ent,'null') || '~' || nvl(v_Seq_Num,'null') );

end;

$$ LANGUAGE plpgsql;

ERROR: relation "c1" does not exist

CONTEXT: compilation of PL/pgSQL function "PR_MIG_STG_I864" near line 22

SQL state: 42P01

Also, "record" variables are similar to row-type variables. I have tried to use the generic type "record" for rec1. When rec1 as record and the rest of my declaration for v_Rec_Num, v_Dat_Ent, and v_Seq_Num of schema.table_name does not exit.

ERROR: invalid type name "cidr_staging.stg_i864.receipt_number%type"

LINE 29: v_Rec_Num cidr_staging.stg_i864.receipt_number%type;
^
SQL state: 42601

Character: 1626

I can’t specify any relation automatically specifies a type called <relation>%rowtype? I appreciate all assistance.