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.
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@xxxxxxxxxxx> 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
> 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
