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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

DECLARE array_var order_list[];

http://www.postgresql.org/docs/9.4/static/arrays.html

Regards

Pavel Stehule

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:

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&gt; = 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:

/messages/by-id/CAK8Zd=t-3TpH7b0h8xHSHjALqBgJBeUTwVjFwVntcZpVXHOxuA@mail.gmail.com

thanks in advance,

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general