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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
