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

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