3

I have a table-valued PL/pgsql function that takes as 1 input an integer, an ID. The table that is returned has fixed columns (say 5) but varying number of rows.

There is a large table of these unique IDs. I'd like to apply this function to each ID and UNION ALL the results.

Looking online I keep seeing CROSS APPLY as the solution, but it does not appear to be available in PostgreSQL. How can I do this "apply" operation?

One trivial solution is to re-write the table-valued function with an additional outer loop. But is there a way to do this directly in SQL?

Ming K
  • 1,117
  • 1
  • 13
  • 20
  • With current Postgres (9.2 or below), instead of looping, you might be able to integrate the table of IDs directly into table function returning a single set. It's may be hard to re-think the logic, but often it can be done in my experience. – Erwin Brandstetter Aug 21 '13 at 15:57

2 Answers2

2

I think it's impossible to do in current version of PostgreSQL (9.2). In 9.3 there would be LATERAL join which does exactly what you want.

You can, however, apply function returning set of simple values:

select id, func(id) as f from tbl1

sql fiddle demo

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
1

SQL Fiddle

create table t (id int);
insert into t (id) select generate_series(1, 10);

create or replace function f (i integer)
returns table(id_2 integer, id_3 integer) as $$
    select id * 2 as id_2, id * 3 as id_3
    from t
    where id between i - 1 and i + 1
$$ language sql;

select id, (f(id)).*
from t;
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • While it looks like it doing what OP wants, in practice it's not. This statement calls f(id) for each column, so result could be surprising, see sqlfiddle - http://sqlfiddle.com/#!12/54d98/2. – Roman Pekar Aug 21 '13 at 19:15
  • @Roman That has nothing to do with the question. Check these queries: http://sqlfiddle.com/#!12/54d98/9 If you run the second one in your machine you will be surprised by the clock_timestamp results. – Clodoaldo Neto Aug 21 '13 at 19:49
  • @Roman What I mean is that while a sequence of random() will always yield the same result, it is not possible (that I know of) to predict in what order Postgresql will execute those random(), that is, the sequence can be different from the literal query order. – Clodoaldo Neto Aug 21 '13 at 19:54
  • what I've tried to test is how much calls to function actually is. I think that if it's on call for each ID, the there should be rows where id2 in second row equal id3 in first and vice versa, and that condition is not hold => I think that function is called for each column - http://sqlfiddle.com/#!12/54d98/13 – Roman Pekar Aug 21 '13 at 19:58
  • @Roman You are right (I guess) when you say that `select (f(id)).id_1, (f(id)).id_2` will call the function twice. But your test is wrong as I showed in my previous comments. I can't believe `select (f(id)).*` will call it more than once. http://sqlfiddle.com/#!12/3b815/1 – Clodoaldo Neto Aug 21 '13 at 20:04
  • Sorry, but I don't see how your example shows that my test is wrong. See another example, it's clearly show that function f called 3 times - one time for each column - http://sqlfiddle.com/#!12/6db68/1 - see duration of execution. Actually it's sad, I'd really like if you solution work. – Roman Pekar Aug 21 '13 at 20:32
  • Yes, multiple evaluations of f(id) happen with (f(id)).*. I've seen it discussed first in [depesz blog post about lateral](http://www.depesz.com/2012/08/19/waiting-for-9-3-implement-sql-standard-lateral-subqueries/) which is an interesting read for this question anyway. – Daniel Vérité Aug 21 '13 at 20:49
  • @Roman There is some subtle difference in our understanding which is preventing a clear communication as I also don't see how your last sample shows it being called 3 times other than the execution time. – Clodoaldo Neto Aug 21 '13 at 22:50
  • @Daniel That post only says _I recall reading about it someplace_ then it says it is possible to demonstrate using `raise notice` but does not do it. – Clodoaldo Neto Aug 21 '13 at 22:53
  • @Clodoaldo: I've made a question on its own for this. See [How to avoid multiple function evals with the (func()).* syntax in an SQL query?](http://stackoverflow.com/questions/18369778/) – Daniel Vérité Aug 22 '13 at 00:27