Re: plpgsql: how to use a composite type varray - Mailing 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?