Обсуждение: 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
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_rowto_char(added, 'Mon D YYYY'),text_col
to_char(updated, 'Mon D YYYY'),
FROM note
WHERE id = $1;
RETURN your_row;
END;
$$ LANGUAGE sql;
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