Обсуждение: 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/