Re: 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(at)gmail(dot)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(at)spamfence(dot)net> writes:
> Joe Kramer <cckramer(at)gmail(dot)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(at)spamfence(dot)net> writes:
> > Joe Kramer <cckramer(at)gmail(dot)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(at)spamfence(dot)net> writes:
> > > Joe Kramer <cckramer(at)gmail(dot)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(at)oss4u(dot)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(at)spamfence(dot)net> writes:
> > > > Joe Kramer <cckramer(at)gmail(dot)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(at)oss4u(dot)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(at)j-davis(dot)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