Using Function returning setof record in JOIN

Поиск
Список
Период
Сортировка
От gmb
Тема Using Function returning setof record in JOIN
Дата
Msg-id 2c673d8c-0202-4295-a673-7656a0c17a20@glegroupsg2000goo.googlegroups.com
обсуждение исходный текст
Ответы Re: Using Function returning setof record in JOIN  ("David Johnston" <polobo@yahoo.com>)
Re: Using Function returning setof record in JOIN  (Merlin Moncure <mmoncure@gmail.com>)
Список 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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Vick Khera
Дата:
Сообщение: Re: PostgreSQL + FreeBSD memory configuration, and an issue
Следующее
От: Shianmiin
Дата:
Сообщение: Re: PostgreSQL backend process high memory usage issue