Thread: plpgsql: how to use a composite type varray

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 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 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;