On Sun, 03 Feb 2008 18:23:47 -0500 brian <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
