Using Function returning setof record in JOIN

From: gmb <gmbouwer(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Using Function returning setof record in JOIN
Date: 2011-04-08 10:57:21
Message-ID: 2c673d8c-0202-4295-a673-7656a0c17a20@glegroupsg2000goo.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Artem Shpynov aka FYR 2011-04-08 11:15:53 Index scan vs table inheritance
Previous Message Gipsz Jakab 2011-04-08 08:00:23 PostgreSQL + FreeBSD memory configuration, and an issue