agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedplpgsql: how to use a composite type varray
6+ messages / 3 participants
[nested] [flat]
* plpgsql: how to use a composite type varray
@ 2015-11-19 08:09 =?gb2312?B?wqy1zw==?= <ludi_1981@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: 卢迪 @ 2015-11-19 08:09 UTC (permalink / raw)
To: 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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: plpgsql: how to use a composite type varray
@ 2015-11-19 09:21 Alexander Shereshevsky <shereshevsky@gmail.com>
parent: =?gb2312?B?wqy1zw==?= <ludi_1981@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Alexander Shereshevsky @ 2015-11-19 09:21 UTC (permalink / raw)
To: 卢迪 <ludi_1981@hotmail.com>; +Cc: pgsql-sql
On Thu, Nov 19, 2015 at 10:09 AM, 卢迪 <ludi_1981@hotmail.com> wrote:
> 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?
>
>
> Hi,
p_dailyinfo_r[] is array of type p_dailyinfo_r.
try something like this to test:
DROP TYPE IF EXISTS p_dailyinfo_r;CREATE TYPE p_dailyinfo_r AS (
fund_account text );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 INSELECT
fund_accountFROM
dailyinfoWHERE
trade_date = '2015-11-01' LOOPBEGIN
i: = i + 1;
e.fund_account : = c.fund_account;IF i > 0AND i < 10 THEN raise notice 'xxx %',
e.fund_account;END IF;END;END LOOP;
raise notice 'total is %',
i;
EXCEPTIONWHEN OTHERS THEN raise EXCEPTION '%',
sqlerrm;END;
$BODY$ LANGUAGE plpgsql;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: plpgsql: how to use a composite type varray
@ 2015-11-19 10:23 =?gb2312?B?wqy1zw==?= <ludi_1981@hotmail.com>
parent: Alexander Shereshevsky <shereshevsky@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: 卢迪 @ 2015-11-19 10:23 UTC (permalink / raw)
To: Alexander Shereshevsky <shereshevsky@gmail.com>; +Cc: pgsql-sql
Hi Alexander,
thank you for reply my email.
My PGADMIN still shows this:ERROR: type "p_dailyinfo_r[]" does not existCONTEXT: compile of PL/pgSQL function "test" near line 2.
I created this type by a test role and run this sciprt by this test role again. Should I check the security options?
BTW: This is my postgresql version. Is this a problem against database version?psql --versionpsql (PostgreSQL) 8.2.15contains support for command-line editing
From: shereshevsky@gmail.com
Date: Thu, 19 Nov 2015 11:21:04 +0200
Subject: Re: [SQL] plpgsql: how to use a composite type varray
To: ludi_1981@hotmail.com
CC: pgsql-sql@postgresql.org
On Thu, Nov 19, 2015 at 10:09 AM, 卢迪 <ludi_1981@hotmail.com> wrote:
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?
Hi, p_dailyinfo_r[] is array of type p_dailyinfo_r.
try something like this to test:
DROP TYPE IF EXISTS p_dailyinfo_r;
CREATE TYPE p_dailyinfo_r AS (
fund_account text );
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;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: plpgsql: how to use a composite type varray
@ 2015-11-19 11:24 Alexander Shereshevsky <shereshevsky@gmail.com>
parent: =?gb2312?B?wqy1zw==?= <ludi_1981@hotmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Alexander Shereshevsky @ 2015-11-19 11:24 UTC (permalink / raw)
To: 卢迪 <ludi_1981@hotmail.com>; +Cc: pgsql-sql
Not security related.
8.2 is very old and not supported:
http://www.postgresql.org/support/versioning/
Can you install newer version?
I tested on 9.4 - works fine.
BR
On Thu, Nov 19, 2015 at 12:23 PM, 卢迪 <ludi_1981@hotmail.com> wrote:
> Hi Alexander,
>
> thank you for reply my email.
>
> My
> PGADMIN still shows this:
> ERROR: type "p_dailyinfo_r[]" does not exist
> CONTEXT: compile of PL/pgSQL function "test" near line 2.
>
> I created this type by a test role and run this sciprt by this test role
> again. Should I check the security options?
>
> BTW: This is my postgresql version. Is this a problem against database
> version?
> psql --version
> psql (PostgreSQL) 8.2.15
> contains support for command-line editing
>
>
>
> ------------------------------
> From: shereshevsky@gmail.com
> Date: Thu, 19 Nov 2015 11:21:04 +0200
> Subject: Re: [SQL] plpgsql: how to use a composite type varray
> To: ludi_1981@hotmail.com
> CC: pgsql-sql@postgresql.org
>
> On Thu, Nov 19, 2015 at 10:09 AM, 卢迪 <ludi_1981@hotmail.com> wrote:
>
> 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?
>
>
> Hi,
>
> p_dailyinfo_r[] is array of type p_dailyinfo_r.
>
> try something like this to test:
>
> DROP TYPE IF EXISTS p_dailyinfo_r;CREATE TYPE p_dailyinfo_r AS (
> fund_account text );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 INSELECT
> fund_accountFROM
> dailyinfoWHERE
> trade_date = '2015-11-01' LOOPBEGIN
> i: = i + 1;
> e.fund_account : = c.fund_account;IF i > 0AND i < 10 THEN raise notice 'xxx %',
> e.fund_account;END IF;END;END LOOP;
> raise notice 'total is %',
> i;
> EXCEPTIONWHEN OTHERS THEN raise EXCEPTION '%',
> sqlerrm;END;
> $BODY$ LANGUAGE plpgsql;
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: plpgsql: how to use a composite type varray
@ 2015-11-19 11:48 =?gb2312?B?wqy1zw==?= <ludi_1981@hotmail.com>
parent: Alexander Shereshevsky <shereshevsky@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: 卢迪 @ 2015-11-19 11:48 UTC (permalink / raw)
To: Alexander Shereshevsky <shereshevsky@gmail.com>; +Cc: pgsql-sql
Sure. thanks. Let me try it.
From: shereshevsky@gmail.com
Date: Thu, 19 Nov 2015 13:24:55 +0200
Subject: Re: [SQL] plpgsql: how to use a composite type varray
To: ludi_1981@hotmail.com
CC: pgsql-sql@postgresql.org
Not security related.8.2 is very old and not supported:http://www.postgresql.org/support/versioning/
Can you install newer version?I tested on 9.4 - works fine.
BR
On Thu, Nov 19, 2015 at 12:23 PM, 卢迪 <ludi_1981@hotmail.com> wrote:
Hi Alexander,
thank you for reply my email.
My PGADMIN still shows this:ERROR: type "p_dailyinfo_r[]" does not existCONTEXT: compile of PL/pgSQL function "test" near line 2.
I created this type by a test role and run this sciprt by this test role again. Should I check the security options?
BTW: This is my postgresql version. Is this a problem against database version?psql --versionpsql (PostgreSQL) 8.2.15contains support for command-line editing
From: shereshevsky@gmail.com
Date: Thu, 19 Nov 2015 11:21:04 +0200
Subject: Re: [SQL] plpgsql: how to use a composite type varray
To: ludi_1981@hotmail.com
CC: pgsql-sql@postgresql.org
On Thu, Nov 19, 2015 at 10:09 AM, 卢迪 <ludi_1981@hotmail.com> wrote:
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?
Hi, p_dailyinfo_r[] is array of type p_dailyinfo_r.
try something like this to test:
DROP TYPE IF EXISTS p_dailyinfo_r;
CREATE TYPE p_dailyinfo_r AS (
fund_account text );
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;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: plpgsql: how to use a composite type varray
@ 2015-11-19 15:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alexander Shereshevsky <shereshevsky@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2015-11-19 15:10 UTC (permalink / raw)
To: Alexander Shereshevsky <shereshevsky@gmail.com>; +Cc: 卢迪 <ludi_1981@hotmail.com>; pgsql-sql
Alexander Shereshevsky <shereshevsky@gmail.com> writes:
> 8.2 is very old and not supported:
... and, according to
http://www.postgresql.org/docs/devel/static/release-8-3.html
arrays of composite types were introduced in 8.3.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2015-11-19 15:10 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-11-19 08:09 plpgsql: how to use a composite type varray =?gb2312?B?wqy1zw==?= <ludi_1981@hotmail.com>
2015-11-19 09:21 ` Alexander Shereshevsky <shereshevsky@gmail.com>
2015-11-19 10:23 ` =?gb2312?B?wqy1zw==?= <ludi_1981@hotmail.com>
2015-11-19 11:24 ` Alexander Shereshevsky <shereshevsky@gmail.com>
2015-11-19 11:48 ` =?gb2312?B?wqy1zw==?= <ludi_1981@hotmail.com>
2015-11-19 15:10 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox