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.
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 |