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 <[email protected]>
wrote:
>
>
> 2015-02-03 13:49 GMT+01:00 Ramesh T <[email protected]>:
>
>> 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,
>>
>>
>>
>>
>
