Is it possible to return custom type as proper ROW?
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.
Joe Kramer <cckramer@gmail.com> schrieb:
I want to get:
item_id | last_update
-------------------------------------
32 | 1234-12-12 12:12:12
Untested:
SELECT item_id, last_update from public.new_item(3,2);
HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�
Andreas Kretschmer <akretschmer@spamfence.net> writes:
Joe Kramer <cckramer@gmail.com> schrieb:
I want to get:
item_id | last_update
-------------------------------------
32 | 1234-12-12 12:12:12
Untested:
SELECT item_id, last_update from public.new_item(3,2);
Or just
SELECT * FROM public.new_item(3,2);
regards, tom lane
am Wed, dem 11.10.2006, um 12:56:51 -0400 mailte Tom Lane folgendes:
Andreas Kretschmer <akretschmer@spamfence.net> writes:
Joe Kramer <cckramer@gmail.com> schrieb:
I want to get:
item_id | last_update
-------------------------------------
32 | 1234-12-12 12:12:12Untested:
SELECT item_id, last_update from public.new_item(3,2);Or just
SELECT * FROM public.new_item(3,2);
Yes, but i have learned, that 'SELECT * ...' is evil...
Thanks for the hint.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Wednesday 11 October 2006 10:42, A. Kretschmer wrote:
am Wed, dem 11.10.2006, um 12:56:51 -0400 mailte Tom Lane folgendes:
Andreas Kretschmer <akretschmer@spamfence.net> writes:
Joe Kramer <cckramer@gmail.com> schrieb:
I want to get:
item_id | last_update
-------------------------------------
32 | 1234-12-12 12:12:12Untested:
SELECT item_id, last_update from public.new_item(3,2);Or just
SELECT * FROM public.new_item(3,2);Yes, but i have learned, that 'SELECT * ...' is evil...
Well, "SELECT *" is only evil if your application relies on a specific column
order to function. The moment you change the table layout and you're using
"select *" your application will cease functioning.
My app uses tons of select *, but then I wrote an object mapper that queries
the information schema at startup - so it's aware of table changes and
adjusts accordingly.
Uwe
--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax: +1 707 568 6416
On 10/11/06, Uwe C. Schroeder <uwe@oss4u.com> wrote:
Well, "SELECT *" is only evil if your application relies on a specific column
order to function. The moment you change the table layout and you're using
"select *" your application will cease functioning.
My app uses tons of select *, but then I wrote an object mapper that queries
the information schema at startup - so it's aware of table changes and
adjusts accordingly.
+1
assumed column ordering is the real enemy. Here is another place
where select * is imo better style than non select *:
select q.*, bar from
(
select a, b,c from foo
) q;
what I really wish sql had was the ability to select all but a
particular column :)
merlin
On Wed, 2006-10-11 at 11:05 -0700, Uwe C. Schroeder wrote:
On Wednesday 11 October 2006 10:42, A. Kretschmer wrote:
am Wed, dem 11.10.2006, um 12:56:51 -0400 mailte Tom Lane folgendes:
Andreas Kretschmer <akretschmer@spamfence.net> writes:
Joe Kramer <cckramer@gmail.com> schrieb:
I want to get:
item_id | last_update
-------------------------------------
32 | 1234-12-12 12:12:12Untested:
SELECT item_id, last_update from public.new_item(3,2);Or just
SELECT * FROM public.new_item(3,2);Yes, but i have learned, that 'SELECT * ...' is evil...
Well, "SELECT *" is only evil if your application relies on a specific column
order to function. The moment you change the table layout and you're using
"select *" your application will cease functioning.
My app uses tons of select *, but then I wrote an object mapper that queries
the information schema at startup - so it's aware of table changes and
adjusts accordingly.
It's aware of the tables as they exist at startup. That may change
between when the mapper looks at the information schema and when it gets
the results of a query.
If you know what it's doing it's probably fine, but that doesn't seem
like a general solution.
Regards,
Jeff Davis
On Thu, 2006-10-12 at 01:36 +0530, Merlin Moncure wrote:
On 10/11/06, Uwe C. Schroeder <uwe@oss4u.com> wrote:
Well, "SELECT *" is only evil if your application relies on a specific column
order to function. The moment you change the table layout and you're using
"select *" your application will cease functioning.
My app uses tons of select *, but then I wrote an object mapper that queries
the information schema at startup - so it's aware of table changes and
adjusts accordingly.+1
assumed column ordering is the real enemy. Here is another place
where select * is imo better style than non select *:select q.*, bar from
(
select a, b,c from foo
) q;
What is "bar"?
Were you trying to show how * can be used when you have already
specified the order in a subquery?
That makes sense to me as long as you always see the order in the query,
and as long as it's always well-defined.
Regards,
Jeff Davis
On Wed, Oct 11, 2006 at 02:08:03PM -0700, Jeff Davis wrote:
select q.*, bar from
(
select a, b,c from foo
) q;What is "bar"?
XMIN, for example
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On 10/12/06, Jeff Davis <pgsql@j-davis.com> wrote:
assumed column ordering is the real enemy. Here is another place
where select * is imo better style than non select *:select q.*, bar from
(
select a, b,c from foo
) q;What is "bar"?
bar is somthing else, a constant, field from related join, or
whetever. Also, i am much more liberal about select * in views,
because the decision about columns is pushed out to the view selector:
create view foobar as
select * from foo natural join bar;
My rationale here is the major point of the view is relating foo to
bar, not choosing columns. Also, if foo/bar gain lose columns, I have
but to drop/recreate the view without changing it's definition. This
makes the view more functionally dependant on the tables.
merlin