plpgsql: how to use a composite type varray

From: 卢迪 <ludi_1981(at)hotmail(dot)com>
To: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: plpgsql: how to use a composite type varray
Date: 2015-11-19 08:09:28
Message-ID: COL128-W93EDC4CCC061CDD1FADFDA851B0@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hello everyone,
In ORACLE, I have this kind of code:PROCEDURE dailyinfo_noexg(p_date IN DATE, p_num_of_date IN NUMBER) IS.....TYPE p_dailyinfo_r IS RECORD( dailyinfo_pk dailyinfo.wc_fund_dailyinfo_pk%TYPE, fund_account dailyinfo.fund_account%TYPE, client_id dailyinfo.client_id%TYPE, fund_code dailyinfo.fund_code%TYPE, trade_date dailyinfo.trade_date%TYPE, apply_amount dailyinfo.apply_amount%TYPE, redeem_amount dailyinfo.redeem_amount%TYPE, daily_return dailyinfo.daily_return%TYPE, total_return dailyinfo.total_return%TYPE, daily_profit dailyinfo.daily_profit%TYPE, latestweekly_yield dailyinfo.latestweekly_yield%TYPE, next_settle_date dailyinfo.next_settle_date%TYPE, sys_created_by dailyinfo.sys_created_by%TYPE, sys_created_date dailyinfo.sys_created_date%TYPE, sys_updated_by dailyinfo.sys_updated_by%TYPE, sys_updated_date dailyinfo.sys_updated_date%TYPE); TYPE p_dailyinfo IS TABLE OF p_dailyinfo_r; p_dailyinfo_t p_dailyinfo;.....beginsome statment and loop statement:p_dailyinfo_t(j).daily_return := 0;p_dailyinfo_t(j).apply_amount := 0;p_dailyinfo_t(j).redeem_amount := 0;......
end;

I tried this in pl/pgsql:This is my composite typeComposite type "yjbtest.p_dailyinfo_r" Column | Type ----------------------+----------------------- dailyinfo_pk | bigint fund_account | character varying(18) client_id | character varying(18) fund_code | character varying(15) trade_date | date apply_amount | numeric(20,3) redeem_amount | numeric(20,3) daily_return | numeric(20,3) total_return | numeric(20,3) daily_profit | numeric(20,5) latestweekly_yield | numeric(20,5) next_settle_date | date sys_created_by | character varying(50) sys_created_date | date sys_updated_by | character varying(50) sys_updated_date | date

pl/pgsql:CREATE OR REPLACE FUNCTION test() RETURNs void AS$BODY$ declare e p_dailyinfo_r; er p_dailyinfo_r[]; i numeric(10); c record; BEGIN i:=0; for c in select fund_account from dailyinfo where trade_date='2015-11-01' loop begin i:=i+1; e.fund_account:=c.fund_account;
if i>0 and i<10 then raise notice 'xxx %',e.fund_account; end if; end; end loop; raise notice 'total is %',i; EXCEPTION WHEN OTHERS THEN raise exception '%',sqlerrm; END; $BODY$ LANGUAGE plpgsql;
The error message in pgadmin is :ERROR: type "p_dailyinfo_r[]" does not existCONTEXT: compile of PL/pgSQL function "test" near line 4
I don't know why the p_dailyinfo_r[] does not exist. I tried varchar[] in test code. And this works.
Could you please help me verify this error?

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Alexander Shereshevsky 2015-11-19 09:21:04 Re: plpgsql: how to use a composite type varray
Previous Message Michael Moore 2015-11-17 00:36:25 Re: Need more info on PL/pgSQL compile listing