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

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"

Further reading led me to:

create function note_get (
    id int,
    out added varchar(12),
    out updated varchar(12),
    out text varchar(1000)
)
returns setof record
...

which got me
ERROR:  CREATE FUNCTION / OUT parameters are not implemented

at which point I thought it best to go out for a walk :)

How do I create a function that returns a number of columns like this?

Thanks
--
Mike

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
);

CREATE FUNCTION get_note(id INT)
RETURNS SETOF your_type IMMUTABLE
AS $$

   DECLARE
     your_row your_type%rowtype;

   BEGIN
     SELECT INTO your_row
       to_char(added, 'Mon D YYYY'),
       to_char(updated, 'Mon D YYYY'),
       text_col
     FROM note
     WHERE id = $1;

     RETURN your_row;
   END;
$$ LANGUAGE sql;

b

On Feb 3, 2008 3:23 PM, brian <brian@zijn-digital.com> wrote:

All's okay, except you should not have declared it IMMUTABLE, because the results depend on a database query.

From the docs:
IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

17° 29' 34.37"N,   78° 30' 59.76"E - Hyderabad
18° 32' 57.25"N,   73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco *

http://gurjeet.frihost.net

Mail sent from my BlackLaptop device

Gurjeet Singh wrote:
>
> All's okay, except you should not have declared it IMMUTABLE, because the
> results depend on a database query.
>
> From the docs:
> IMMUTABLE indicates that the function cannot modify the database and always
> returns the same result when given the same argument values; that is, it does
> not do database lookups or otherwise use information not directly present in
> its argument list
>

Yes, my bad. Make that STABLE.

b

On Sun, 03 Feb 2008 18:23:47 -0500
brian <brian@zijn-digital.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,
whichis 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
withlots of joins... 

Anyway, thanks for your help.
-
Mike