Обсуждение: SQL Server stored procedures?
Hello- I have been trying for a while to write a function that would return multiple rows and columns in a similar fashion as SQL server stored procedures. I can get my functions to return multiple rows and columns but only from a single table. I worked around this by creating a view that ties all my tables together like so (basic example)-><fontfamily><param>Courier</param><x-tad-bigger> CREATE FUNCTION usp_grab_classes() RETURNS SETOF classes AS 'SELECT classes.* FROM classes' LANGUAGE 'sql'; </x-tad-bigger></fontfamily>I am curious if there is a way to select and return multiple columns from multiple tables without having to first create a view. Any help would greatly appreciated. Todd Marek<fontfamily><param>Courier</param><x-tad-bigger> </x-tad-bigger></fontfamily><italic>"If you think you understand something it's habit." --Gary Kraftsow--</italic> Hello- I have been trying for a while to write a function that would return multiple rows and columns in a similar fashion as SQL server stored procedures. I can get my functions to return multiple rows and columns but only from a single table. I worked around this by creating a view that ties all my tables together like so (basic example)-> CREATE FUNCTION usp_grab_classes() RETURNS SETOF classes AS 'SELECT classes.* FROM classes' LANGUAGE 'sql'; I am curious if there is a way to select and return multiple columns from multiple tables without having to first create a view. Any help would greatly appreciated. Todd Marek "If you think you understand something it's habit." --Gary Kraftsow--
On Mon, Nov 29, 2004 at 07:35:57PM -0600, Todd P Marek wrote: > I am curious if there is a way to select and return multiple columns > from multiple tables without having to first create a view. You could use CREATE TYPE to create a composite type with the desired fields and return SETOF that type. When I do this, I sometimes create the type to contain only keys; if I want additional columns then I join the function results (rows of keys) to the appropriate tables. Depending on why you object to creating a view to get a composite type, you might not want to create a custom type either. The function could return SETOF RECORD, but then queries that use it will have to supply their own column definitions. I prefer to do this only when the return rows don't have a fixed format. -- Michael Fuhr http://www.fuhr.org/~mfuhr/