Thread: how to create a composite type as return type for a Function as Table Source

Hello,

I want to make a Function as table source. As rettype you have to give a
composite type but how do I make those? I'm using an new empty table with
the the datatypes (INT, INT8, TEXT and a TIMESTAMP) I need.

Is this the normal way how to make composite types?

Example:

CREATE TABLE bigint_int_text_timestamp (
    column_1 INT8,
    column_2 INT,
    column_3 TEXT,
    column_4 TIMESTAMP);


CREATE OR REPLACE FUNCTION get_reservations (int) RETURNS SETOF
bigint_int_text AS $$

SELECT     r.id,
                   p.id,
                   description,
                   last_time
FROM       reservations r,
                  persons p
WHERE    product_id =  $1

$$ LANGUAGE SQL;

On Thu, 9 Dec 2004, Tjibbe Rijpma wrote:

> I want to make a Function as table source. As rettype you have to give a
> composite type but how do I make those? I'm using an new empty table with
> the the datatypes (INT, INT8, TEXT and a TIMESTAMP) I need.
>
> Is this the normal way how to make composite types?
>
> Example:
>
> CREATE TABLE bigint_int_text_timestamp (
>     column_1 INT8,
>     column_2 INT,
>     column_3 TEXT,
>     column_4 TIMESTAMP);

I'd normally use CREATE TYPE AS.

CREATE TYPE bigint_int_text_timestamp AS (
 column_1 int8,
 column_2 int,
 column_3 text,
 column_4 timestamp);

"Tjibbe Rijpma" <t.b.rijpma@student.tudelft.nl> writes:
> I want to make a Function as table source. As rettype you have to give a
> composite type but how do I make those?

Best way is

CREATE TYPE typename AS (colname coltype, colname coltype, ...);

> Is this the normal way how to make composite types?

> CREATE TABLE bigint_int_text_timestamp (
>     column_1 INT8,
>     column_2 INT,
>     column_3 TEXT,
>     column_4 TIMESTAMP);

You can do that too, since a table declaration creates an associated
rowtype.  But if you only intend to use the rowtype and not the actual
table, I think it's more understandable to declare the type as a type.

            regards, tom lane