Re: Using Function returning setof record in JOIN - Mailing list pgsql-general

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of gmb
Sent: Friday, April 08, 2011 6:57 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] 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@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general