Re: Function returning subset of columns from table (return type)

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