Обсуждение: %rowtype

Поиск
Список
Период
Сортировка

%rowtype

От
Pepe TD Vo
Дата:
Hello,

Would you please tell me what I did wrong here?  I couldn't find the rowtype eliminate with a cursor in multi-tables selected.


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 17
SQL state: 42P01
 
 I found the %rowtype could declare of the table%rowtype,myrow tablename%ROWTYPE; but my oracle script had union tables, how can I declare it?  I have tried declare rec1 stg%rowtype or prod%rowtype and/or the cidr_staging.stg_i864%rowtype... the error is still same, relation does not exist.

Any helps and explanation would be appreciated.  

v/r,

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success

Re: %rowtype

От
Laurenz Albe
Дата:
Pepe TD Vo wrote:
> Would you please tell me what I did wrong here?
> I couldn't find the rowtype eliminate with a cursor in multi-tables selected.
> 
> 
> CREATE OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_I864" ( v_Ret OUT int ) RETURNS integer as $$
>  declare 
>    c1 cursor is
>       SELECT ...;
>    rec1            c1%rowtype;

There is a type associated with each view and table, but not with a cursor.

You need to use the generic type "record" for "rec1".

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com



Re: %rowtype

От
Pepe TD Vo
Дата:
thank you Ms. Albe,

I have tried that and still not working.  It passed that error and then complained about the shema, cidr_staging does not exist and/or c1 (cursor) is not exist.  when I take it out, it complained invalid type name for those below:
   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;



PL/pgSQL variables can have any SQL data type, such as integervarchar, and char.
Here are some examples of variable declarations:
user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD;
 
again, none of variable declaration as same as example work.  Sorry, I'm not a developer and I'm new on Postgres, sorry for ask a wrong question here when I'm not know how.

thank you,

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Friday, November 9, 2018 2:26 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:


Pepe TD Vo wrote:

> Would you please tell me what I did wrong here?
> I couldn't find the rowtype eliminate with a cursor in multi-tables selected.
>
>
> CREATE OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_I864" ( v_Ret OUT int ) RETURNS integer as $$
>  declare
>    c1 cursor is
>      SELECT ...;
>    rec1            c1%rowtype;


There is a type associated with each view and table, but not with a cursor.

You need to use the generic type "record" for "rec1".

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com




Re: %rowtype

От
Laurenz Albe
Дата:
Pepe TD Vo wrote:
> thank you Ms. Albe,

I am male, but never mind.

> I have tried that and still not working.  It passed that error and then complained about the shema, cidr_staging does
notexist and/or c1 (cursor) is not exist.  when I take it out, it complained invalid type name for those below:
 
>    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;

Works fine for me:

CREATE TABLE laurenz.test(id integer);

DO $$DECLARE
   t laurenz.test.id%TYPE;
BEGIN
   t := 1;
END;$$;

You should probably share the rest of your code + the exact error messages
so we can spot the error.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com