August 28th, 2003, 03:51 AM
Use what type to return 2 tables' record?
Can anyone tell me what type should i use when return 2 tables' record?
CREATE FUNCTION public."DispCustomer"() RETURNS SETOF ??? AS '
FROM "Customer" INNER JOIN "Address" ON "Customer"."CustID" = "Address"."CustID"
' LANGUAGE 'sql' VOLATILE;
The SQL statement will return about 30 fields.
Last edited by ptmhki; August 28th, 2003 at 03:55 AM.
August 31st, 2003, 09:55 PM
Read the following:
And, read Section 9.2 from the documentation: http://www.postgresql.org/docs/7.3/static/xfunc.html
Essentially, SETOF means you are returning anything that can fit into the definition of a "set". So, you can put a table name in there, or a view name, or you can refer to a custom composite type that you have created with the CREATE TYPE command.
Note: CREATE TYPE is a little confusing in PostgreSQL, because it does double-duty:
1. creating custom base(scalar) types, where you define the functions for raw input, storage, internal and external represention
2. creating composite or "dummy" types which are really just like table definitions-- simply a list of attributes associated with existing types. The two are very different. The first kind of type can be used as custom datatypes for actual data stored in tables, while the second kind (composite) are only for use in set-returning functions.
So, if you are returning a set based on your multi-table example query you posted, you would just create a composite type that would encompass all the attributes being returned from this query.
Of course, if you are seriously suggesting writing a procedure to do the query above, you would be much better off just creating a view. Or, create a view which handles the basic join, and then use a function to return SETOF [view_name] for further manipulation.