#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Posts
    5
    Rep Power
    0

    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 '
    SELECT *
    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.
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Read the following:

    http://techdocs.postgresql.org/guide...rningFunctions
    http://www.postgresql.org/docs/7.3/s...functions.html

    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.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo