Re: postgres cust types

[Ramesh T <rameshparnanditech@xxxxxxxxx>]

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.stehule@xxxxxxxxx> wrote:

2015-02-03 13:49 GMT+01:00 Ramesh T <rameshparnanditech@xxxxxxxxx>:

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,