Обсуждение: postgres cust types

Поиск
Список
Период
Сортировка

postgres cust types

От
Ramesh T
Дата:
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 

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,



Re: postgres cust types

От
Pavel Stehule
Дата:


2015-02-03 13:49 GMT+01:00 Ramesh T <rameshparnanditech@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

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,




Re: postgres cust types

От
Adrian Klaver
Дата:
On 02/14/2015 08:58 AM, Ramesh T wrote:
> 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 <http://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..?

See my previous responses to this question:

http://www.postgresql.org/message-id/CAK8Zd=t-3TpH7b0h8xHSHjALqBgJBeUTwVjFwVntcZpVXHOxuA@mail.gmail.com

>
> thanks in advance,

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: 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.stehule@gmail.com> wrote:


2015-02-03 13:49 GMT+01:00 Ramesh T <rameshparnanditech@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

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,