From: | Myk <myk(at)waxfrenzy(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Function returning subset of columns from table (return type) |
Date: | 2008-02-04 02:05:47 |
Message-ID: | 20080204020547.6073aa11.myk@waxfrenzy.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, 03 Feb 2008 18:23:47 -0500
brian <brian(at)zijn-digital(dot)com> wrote:
> Myk wrote:
> > Hi
> >
> > I'm pretty new to PostgreSQL, and have encountered a bit of trouble
> > with functions, namely the return type. Version is 8.0.15.
> >
> > I have the following table:
> >
> > note ( id int, added date, updated date, text varchar(1000) )
> >
> > and want to define a function that just returns the dates and text by
> > id. I initially just did:
> >
> > create function note_get (id int) returns setof note as 'select *
> > from note where id=$1' language sql;
> >
> > which was fine. Then later I thought I'd try formatting the columns
> > (they're only intended for display):
> >
> > create function note_get ( id int ) returns setof record as ' select
> > to_char (added, ''Mon D YYYY''), to_char (updated, ''Mon D YYYY''),
> > text from note where id=$1 ' language sql;
> >
> > but this gives me ERROR: a column definition list is required for
> > functions returning "record"
> >
>
> You could create a rowtype for this:
>
> CREATE TYPE your_type
> AS (
> added CHAR(11) NOT NULL,
> updated CHAR(11) NOT NULL,
> text_col TEXT
> );
After my refreshing walk, I created a view that did the pretty printing, and then just used that:
create function note_get(id int)
returns setof <view_name>
as '
select * from <view_name> where note_id=$1' language sql;
although I then get a redundant note_id, and it may affect performance as the view gets all the 'pretty print' results, which is then filtered afterwards, I'm not sure... (explain analyze is my friend!)
Defining a type as you suggest seems like the proper way - but it's a bit high-maintenance, especially for big queries with lots of joins...
Anyway, thanks for your help.
-
Mike
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2008-02-04 02:21:10 | Re: PostgreSQL Certification |
Previous Message | Greg Smith | 2008-02-04 01:43:28 | Re: Performance problems with Postgresql/ZFS/Non-global zones on Solaris? |