plpgsql: how to use a composite type varray - Mailing list pgsql-sql
| From | 卢迪 |
|---|---|
| Subject | plpgsql: how to use a composite type varray |
| Date | |
| Msg-id | COL128-W93EDC4CCC061CDD1FADFDA851B0@phx.gbl Whole thread Raw |
| Responses |
Re: plpgsql: how to use a composite type varray
|
| List | 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;
.....
begin
some 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 type
Composite 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 exist
CONTEXT: 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?