Thread: 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:12 > > > Untested: > > 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:12 > > > > > > Untested: > > > 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:12
> > > >
> > > > Untested:
> > > > 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