Is it possible to return custom type as proper ROW?

From: "Joe Kramer" <cckramer(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Is it possible to return custom type as proper ROW?
Date: 2006-10-11 15:26:09
Message-ID: b4c00a110610110826n21c4ac8dl221b492f517bbe55@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Pgsql 8.1.4.

I want return custom type from function as row, not as values in brackets (1,2).

I have following type and function:

CREATE TYPE new_item_return_type AS
(item_id bigint,
last_update timestamp without time zone);

CREATE OR REPLACE FUNCTION new_item( new_title int8, new_user_id int8)
RETURNS new_item_return_type AS
$BODY$
DECLARE
ret new_item_return_type%ROWTYPE;
BEGIN
INSERT INTO item (user_id,title) VALUES (new_user_id,new_title) ;
ret.item_id:= currval('item_id_seq');
SELECT time_last_update INTO ret.last_update FROM item WHERE id
=ret.item_id;
RETURN ret;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Seems like in DECLARE ret new_item_return_type%ROWTYPE;
%ROWTYPE is ignored.

When I run SELECT public.new_item(3,2);
I get :
new_item_return_type
---------------------------------
"(32,"2006-10-11 10:14:39")"

I want to get:
item_id | last_update
-------------------------------------
32 | 1234-12-12 12:12:12

Is it possible ? I am using the wrong approach?

Thanks.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sreejesh O S 2006-10-11 15:26:53 Re: Clarification needed
Previous Message Andrew Sullivan 2006-10-11 15:18:28 Re: more anti-postgresql FUD