Thread: Is it possible to return custom type as proper ROW?

Is it possible to return custom type as proper ROW?

From
"Joe Kramer"
Date:
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.

Re: Is it possible to return custom type as proper ROW?

From
Andreas Kretschmer
Date:
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°

Re: Is it possible to return custom type as proper ROW?

From
Tom Lane
Date:
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

Re: Is it possible to return custom type as proper ROW?

From
"A. Kretschmer"
Date:
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

Re: Is it possible to return custom type as proper ROW?

From
"Uwe C. Schroeder"
Date:
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

Re: Is it possible to return custom type as proper ROW?

From
"Merlin Moncure"
Date:
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

Re: Is it possible to return custom type as proper ROW?

From
Jeff Davis
Date:
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


Re: Is it possible to return custom type as proper ROW?

From
Jeff Davis
Date:
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


Re: Is it possible to return custom type as proper ROW?

From
Karsten Hilbert
Date:
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

Re: Is it possible to return custom type as proper ROW?

From
"Merlin Moncure"
Date:
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