Re: [GENERAL] postgres cust types

[Ramesh T]

exactly what I am trying convert oracle to postgres ,
following
1)first i am creating type in oracle
 CREATE  TYPE suborder_list AS (suborder_id int);

2)second creating table type in oracle
create or replace type suborder_list_table as table of suborder_list;

3)i am using above 1 and 2 created types oracle function

create or replace FUNCTION check(id int)
  RETURNS suborder_list_table
is
  BEGIN
   v_ret :=suborder_list_table();

    FOR VAR_CUR1 IN cur1
    LOOP
      invcount:=0;
      SELECT COUNT(id)
      INTO invcount
      FROM detail  iv
      WHERE iv.id  = id
      AND cd IN
        (SELECT cd
        FROM detail)
                  IF (invcount>0) THEN
        v_ret.extend;
        v_ret(v_ret.count) := suborder_list(VAR_CUR1.id);
      END IF;

    END LOOP;
    RETURN v_ret;


 here cur1 is cursor

above 1 and 2 used in 3'rd step of oracle function,now i need to convert
oracle function into postgres format

any help..?

thanks in advance,




On Mon, Feb 9, 2015 at 11:39 AM, Pavel Stehule <pavel.steh...@gmail.com>
wrote:

>
>
> 2015-02-03 13:49 GMT+01:00 Ramesh T <rameshparnandit...@gmail.com>:
>
>> Hi ,
>>       i created type on postgres
>>                              CREATE  TYPE order_list AS (order_id bigint);
>> it works fine.
>>
>> then, i try to create a other table type using above created type.
>> like,
>> --create or replace type suborder_list_table as table of suborder_list;
>> this on *oracle *formate
>>
>
> This syntax is not supported in Pg - resp. a collections are not supported
> by PostgreSQL.
>
> use a arrays instead
>
> DECLARE array_var order_list[];
>
> http://www.postgresql.org/docs/9.4/static/arrays.html
>
> Regards
>
> Pavel Stehule
>
>
>>
>> i need to convert *postgres *and how to create a table type in postgres
>> is it possible
>> or
>> else any other method.
>>
>> FYI,i am using these types in a function.
>>
>> thanks in advance,
>>
>>
>>
>>
>