Function returning setof taking parameters from another table

Поиск
Список
Период
Сортировка
От Marcin Stępnicki
Тема Function returning setof taking parameters from another table
Дата
Msg-id 179149fe0807310603u2be9f793rfba31cfd7d92032e@mail.gmail.com
обсуждение исходный текст
Ответы Re: Function returning setof taking parameters from another table  (Craig Ringer <craig@postnewspapers.com.au>)
Список pgsql-sql
Hello.

I've got a function which returns set of records:

select * from f_test(123);

param |  val1 |  val2
--------------------------- 123   |   1  |  17 123   |   2   |  18



I'd like to execute it multiple times with parameters from other
query, like (it doesn't work of course):

select * from f_test(x.id)
join x on (x.id in (123,124,125));

in order to get:

param |  val1 |  val2
--------------------------- 123   |   1   |  17 123   |   2   |  18 124   |   4   |  179 125   |   13 |  18 125   |
15|  12 125   |   14 |  112
 

So far the only method I can think of is to use union all with
different parametrs, like:

select * from f_test(123)
union all
select * from f_test(124)
union all
select * from f_test(125);

But it is not flexible, I'd like to have parameters stored in another table.

I can also write a pl/pg function, create a string like this with
unions and EXECUTE it. However, it seems ugly to me.

Is there another way?

Regards,
Marcin


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

Предыдущее
От: Richard Huxton
Дата:
Сообщение: Re: Identifying which column matches a full text search
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Problem with ORDER BY and DISTINCT ON