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.

Search Discussions

  • David Johnston at Apr 8, 2011 at 2:36 pm
    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
    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
  • Merlin Moncure at Apr 8, 2011 at 2:41 pm

    On Fri, Apr 8, 2011 at 5:57 AM, gmb wrote:
    Hi

    Is it possible to do a JOIN between a VIEW and the output of a FUNCTION?
    yes.

    merlin
  • Pavel Stehule at Apr 8, 2011 at 2:59 pm

    2011/4/8 Merlin Moncure <mmoncure@gmail.com>:
    On Fri, Apr 8, 2011 at 5:57 AM, gmb wrote:
    Hi

    Is it possible to do a JOIN between a VIEW and the output of a FUNCTION?
    yes.
    yes, it is possible. Just I am not sure if original query wasn't
    directed to >>lateral<< feature.

    Pavel


    merlin

    --
    Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-general

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedApr 8, '11 at 11:23a
activeApr 8, '11 at 2:59p
posts4
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase