I do not know the answer but it isn't that difficult to use trial-and-error to check and see whether the TWO most logical forms would work and then ask for further assistance if they do not. Just pretend you have a view with the same name as your function (though you will need to add the parenthesises) and write the queries normally. As a hint the second form uses an inline view definition [ ... FROM ( SELECT * FROM relation ) alias ... ] This presumes you know how to do normal joins (i.e., between two tables or views). If you do not then you will find the documentation to be of great and timely value. You should find that both versions work but the "inline view" form most definitely will whereas the "direct" form should but I haven't ever attempted to use that form before so I cannot say for certain. David J. -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of gmb Sent: Friday, April 08, 2011 6:57 AM To: pgsql-general@xxxxxxxxxxxxxx Subject: Using Function returning setof record in JOIN Hi Is it possible to do a JOIN between a VIEW and the output of a FUNCTION? e.g. I have a function returning a SETOF records (using OUT parameters) with the following output: testdb=# SELECT * FROM myfunc( 'AAA1' ); -- returns calculcated values for all orders for account 'AAA1' _acc | _order | _date | _calc_amount --------+------------+-------------+---------- AAA1 | ORDER_1 | 2010-12-13 | 1000.00 AAA1 | ORDER_2 | 2010-12-13 | 80.00 AAA1 | ORDER_5 | 2010-12-13 | 10.00 (the example is oversimplified - _calc_amount is one of many calculated values returned by the funtion) I also have a VIEW returning the following: testdb=# SELECT _accno, _client, _deladdress, _orderno FROM orders; _accno | _client | _deladdress | _orderno | ....more order related data --------+------------+---------------+------------+--------------------- --------+------------+---------------+------------+--------------------- --------+------------+---------------+------------+--- AAA1 | JOHN | SMITH STR | ORDER_1 | AAA1 | JOHN | MAIN STR | ORDER_2 | AAA1 | JOHN | PARK RD | ORDER_5 | CCC1 | CHARLIE | 2ND STR | ORDER_3 | BBB1 | BENN | 5TH AVE | ORDER_4 | I want to do a JOIN resulting in: _acc | _order | _date | _amount | _client | _deladdress |....more order related data --------+------------+-------------+-----------+------------+--------------- +------------------------------ AAA1 | ORDER_1 | 2010-12-13 | 1000.00 | JOHN | SMITH STR | AAA1 | ORDER_2 | 2010-12-13 | 80.00 | JOHN | MAIN STR | AAA1 | ORDER_5 | 2010-12-13 | 10.00 | JOHN | PARK RD | Hope this is possible. Thanks in advance. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
