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

On 02/03/2015 04:49 AM, Ramesh T wrote:
> 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

The above makes no sense, you are using a different type.

>
> i need to convert *postgres *and how to create a table type in postgres
> is it possible

In Postgres tables already have a type, hence:

http://www.postgresql.org/docs/9.3/interactive/sql-createtype.html

"If a schema name is given then the type is created in the specified
schema. Otherwise it is created in the current schema. The type name
must be distinct from the name of any existing type or domain in the
same schema. (Because tables have associated data types, the type name
must also be distinct from the name of any existing table in the same
schema.)"

> or
> else any other method.
>
> FYI,i am using these types in a function.

Some explanation of exactly what you are trying to do, in other words
code, would be helpful.

>
> thanks in advance,
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 02/03/2015 07:50 AM, Ramesh T wrote:

Am CCing the list.

> CREATE  TYPE order_list AS (order_id bigint);
> i created above type

Not sure that the above does anything.

>
> and i am using order_list, trying  creating table type (datatype)
>
> *create or replace type order_list_table as table of order_list;*
>
> it is in oracle formate i need to convert this into postgres type.
>
>
> why because i am using order_list_table in function to gather setof
> values return by function.

Except in the below you are using another type?

>
> sample program ,
> create or replace FUNCTION choose(
>      id1 IN bigint,
>      id2 IN  bigint,
>      id3  IN character(2))
>    RETURNS suborder_list_table
> IS
>    v_ret suborder_list_table;
>
>
> here i'm using v_ret for return set of values to return..

Not sure what you are trying to do. Might want to take a look at:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS

In particular the RETURNS TABLE example.

>
>



--
Adrian Klaver
adrian.klaver@aklaver.com