Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gL6am-0005rn-Rk for pgsql-admin@arkaria.postgresql.org; Fri, 09 Nov 2018 13:12:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gL6al-0006rK-CH for pgsql-admin@arkaria.postgresql.org; Fri, 09 Nov 2018 13:12:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gL6ak-0006iQ-D6 for pgsql-admin@lists.postgresql.org; Fri, 09 Nov 2018 13:12:54 +0000 Received: from sonic312-20.consmr.mail.bf2.yahoo.com ([74.6.128.82]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gL6ag-0006Fw-EQ for pgsql-admin@postgresql.org; Fri, 09 Nov 2018 13:12:53 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1541769167; bh=RukgsJR+qV+cZapEoOwTezFDkev+eOwXP8m4fJqOBwo=; h=Date:From:Reply-To:To:In-Reply-To:References:Subject:From:Subject; b=PEVAavJL8O6uyeIdIftNZZB/Z4rDI+rSF1ZjM7hBXxfNMjgd0xR1euzPwGBoKaVNPeV9nWxcJg8BwK38jDbys7ha6EKkBvP8rDJUm8HXzj/PSuTj61LD8LXKUm2GYHphWSX3okTbB2o8YpRkF8kdxbvMl9UCImyROVGzy98oLNqHmG3zbn3F28cIxx6gC2cj4/9lis2sSR+6zjii7xcjT+b7L+J4I6W4Qaijv/+JnWe9i6/DMIHF9ggieVMvC5OrmnsaqmBG1BvOul32wj/TGU6HdWltiptqWu/08ULzZc1ksoeONC9PqGDKBVnLe8rN5hSxGaHIkgX3+SeGiwfZgQ== X-YMail-OSG: QenlSZMVM1kjg2tiTig4AWQklmrUvtbpGWMl7W2J_F7p9AbzAPybRlNGGNtdhMb k441WjrTXAHjchdB4_H037lZkkBskMdmp9rZx_0VCn7bD7VowJjy40QuyEPABKBVl42QNuj4__L1 yxHgydrUL1YhpGv5RoZJCTzrmHAd4ATZuy0DsntF9pfY.brSzgoRu_H0QUkITIXE0LqSranMwmmt _cqya8xcSOaX4gckI_cH4wPnofzoJ3BFbiq2zaH3VUg0HjF8Bnv7py9qlba.7lEhimyPJc2519Jw hpc6z6m1w4YcX_lDYfTZz9pWwIvXtX9VHod04PhKbUaFoLBiRLYUbUGOPCQQTZWHi2UO3O1KgMzb yQa46f0srM0FHzaRrIMJO_UK8c93mOt7NArBhTWfbBisiZzyrt48Y7qhJB3VXuztqNggSpVPCziR FRC3IeaobwTdCfVCX3m3HglrSPmdz4pxCLbuhPcxu9LGxabAV_5EhBstA2mMF0T6fcgJxyfQ6W6B zqybkpJjcvXbPg4suWJ9uFfYDYtpZvLSDSZaGI.Zh9OIwvtB8LJ4za8oiQUd_f0GnxkWBA1_VFl. CQUZJV5MOMUARJ_WpS2oSLbbi4_p4OMzybCxPrZOWvV7ON96zs1NnGOuibI7cIZ58tmtk8ZGKBID r92SkoP0FULlnXcPJoRsWjg7zmD9AARz3rsriylScNDXvU_rX59EuoVeB43Cyd39xnOkvkm90.MG GZEhcs172uwOdQvLQHts2LoD5RLIEeGONpQyL4oZ7NJoNUbpTUzp5xjLscXyIfjMigVa5uisEq91 Mm0m_OyReX9VEBRAYPy2kTyS8BgwWsHUHBZahxZixJufFCzZo6tWKtRBciZRnvE07lEUBhm0zChS ELXD5olSO0CXpWtXdL4NlsCNzjjeH76Awt3Ks0mFTSXSDPKCIatWZcU5vPzfhe7GXxH4HpsulJH3 nyQMnh9AAtlz8YLaww_u1bhF.bHgLCc2OEtcPYkBUYRvu4sKjUqMR3uCQwWxe3hk2yCNl0A61aj8 Oekg- Received: from sonic.gate.mail.ne1.yahoo.com by sonic312.consmr.mail.bf2.yahoo.com with HTTP; Fri, 9 Nov 2018 13:12:47 +0000 Date: Fri, 9 Nov 2018 13:12:45 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Laurenz Albe , Pgsql-admin Message-ID: <1072312432.1472810.1541769165531@mail.yahoo.com> In-Reply-To: References: <1769600488.1326361.1541732091653.ref@mail.yahoo.com> <1769600488.1326361.1541732091653@mail.yahoo.com> Subject: Re: %rowtype MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1472809_1797879210.1541769165526" X-Mailer: WebService/1.1.12710 YahooMailNeo Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.100 Safari/537.36 Content-Length: 10922 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_1472809_1797879210.1541769165526 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable thank you Ms. Albe, I have tried that and still not working.=C2=A0 It passed that error and the= n complained about the shema, cidr_staging does not exist and/or c1 (cursor= ) is not exist.=C2=A0 when I take it out, it complained invalid type name f= or those below:=C2=A0 =C2=A0v_Rec_Num=C2=A0 =C2=A0 =C2=A0 cidr_staging.stg_= i864.receipt_number%type;=C2=A0 =C2=A0v_Dat_Ent=C2=A0 =C2=A0 =C2=A0 cidr_st= aging.stg_i864.date_entered%type;=C2=A0 =C2=A0v_Seq_Num=C2=A0 =C2=A0 =C2=A0= cidr_staging.stg_i864.sequence_number%type; according to this url,=C2=A0https://www.postgresql.org/docs/10/plpgsql-decl= arations.html PL/pgSQL=C2=A0variables can have any SQL data type, such as=C2=A0integer,= =C2=A0varchar, and=C2=A0char.Here are some examples of variable declaration= s:user_id integer; quantity numeric(5); url varchar; myrow tablename%ROWTYPE; myfield tablename.columnname%TYPE; arow RECORD;=C2=A0again, none of variable declaration as same as example wo= rk.=C2=A0 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.=C2=A0 If you avoid people for the= ir 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 t= o 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=20 On Friday, November 9, 2018 2:26 AM, Laurenz Albe wrote: =20 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 selec= ted. >=20 >=20 > CREATE OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_I864" ( v_Ret OUT i= nt ) RETURNS integer as $$ >=C2=A0 declare=20 >=C2=A0 =C2=A0 c1 cursor is >=C2=A0 =C2=A0 =C2=A0 SELECT ...; >=C2=A0 =C2=A0 rec1=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 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 --=20 Cybertec | https://www.cybertec-postgresql.com =20 ------=_Part_1472809_1797879210.1541769165526 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
thank you Ms. Albe,

I have tried that and still not working.  It pass= ed that error and then complained about the shema, cidr_staging does not ex= ist and/or c1 (cursor) is not exist.  when I take it out, it complaine= d invalid type name for those below:
   v_Rec_Num    &nbs= p; cidr_staging.stg_i864.receipt_number%type;
   v_Dat_Ent    &n= bsp; cidr_staging.stg_i864.date_entered%type;
   v_Seq_Num      cid= r_staging.stg_i864.sequence_number%type;



PL/pgSQL variables can have any SQL data type, such as = ;integer, varchar, and char.
Here a= re 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 perfe= ct.  If you avoid people for their mistakes you will be alone. So judg= e less, love and forgive more.
To call him a dog hard= ly seems to do him justice though in as much as he had four legs, a tail, a= nd barked, I admit he was, to all outward appearances. But to those who kne= w him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care= deeply **Speak kindly.
*** Genuinely rich *** Faithful tale= nt *** Sharing success


=
On Friday, November 9, 2018 2:26 AM, Laurenz Albe &l= t;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 "C= IDR_STAGING"."PR_MIG_STG_I864" ( v_Ret OUT int ) RETURNS integer as $$
>  declare
>    c1 cu= rsor is
>      SELECT ...;
>    rec1            c1%r= owtype;


There is a type associat= ed 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-pos= tgresql.com




<= /div> ------=_Part_1472809_1797879210.1541769165526--