pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feed%rowtype
4+ messages / 2 participants
[nested] [flat]
* %rowtype
@ 2018-11-09 02:54 Pepe TD Vo <pepevo@yahoo.com>
2018-11-09 07:26 ` Re: %rowtype Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: Pepe TD Vo @ 2018-11-09 02:54 UTC (permalink / raw)
To: pgsql-admin
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 existCONTEXT: compilation of PL/pgSQL function "PR_MIG_STG_I864" near line 17SQL 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: %rowtype
2018-11-09 02:54 %rowtype Pepe TD Vo <pepevo@yahoo.com>
@ 2018-11-09 07:26 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-11-09 13:12 ` Re: %rowtype Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2018-11-09 07:26 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: %rowtype
2018-11-09 02:54 %rowtype Pepe TD Vo <pepevo@yahoo.com>
2018-11-09 07:26 ` Re: %rowtype Laurenz Albe <laurenz.albe@cybertec.at>
@ 2018-11-09 13:12 ` Pepe TD Vo <pepevo@yahoo.com>
2018-11-09 16:40 ` Re: %rowtype Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: Pepe TD Vo @ 2018-11-09 13:12 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin
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;
according to this url, https://www.postgresql.org/docs/10/plpgsql-declarations.html
PL/pgSQL variables can have any SQL data type, such as integer, varchar, 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: %rowtype
2018-11-09 02:54 %rowtype Pepe TD Vo <pepevo@yahoo.com>
2018-11-09 07:26 ` Re: %rowtype Laurenz Albe <laurenz.albe@cybertec.at>
2018-11-09 13:12 ` Re: %rowtype Pepe TD Vo <pepevo@yahoo.com>
@ 2018-11-09 16:40 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2018-11-09 16:40 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; pgsql-admin
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 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;
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-11-09 16:40 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-11-09 02:54 %rowtype Pepe TD Vo <pepevo@yahoo.com>
2018-11-09 07:26 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-11-09 13:12 ` Pepe TD Vo <pepevo@yahoo.com>
2018-11-09 16:40 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox