Call a Set-Returning Function with an Array Argument Multiple Times

Call a set-returning function with an array argument multiple times

In Postgres 9.3 or later, it's typically best to use LEFT JOIN LATERAL ... ON true:

SELECT sub.dataid, f.*
FROM  (
   SELECT dataid, array_agg(data) AS arr
   FROM   dataset
   WHERE  dataid = something
   GROUP  BY 1
   ) sub
LEFT   JOIN LATERAL foo(sub.arr) f ON true;

If the function foo() can return no rows, that's the safe form as it preserves all rows to the left of the join, even when no row is returned to the right.

Else, or if you want to exclude rows without result from the lateral join, use:

CROSS JOIN LATERAL foo(sub.arr)

or the shorthand:

, foo(sub.arr)

There is an explicit mention in the manual.

Craig's related answer (referenced by Daniel) is updated accordingly:

• How to avoid multiple function evals with the (func()).* syntax in an SQL query?

plpgsql function that returns multiple columns gets called multiple times

This should do the job:

SELECT (y).*
FROM  (
   SELECT my_aggregate_function(border, lower_limit, upper_limit, operation) AS y
   FROM (
      SELECT (x).*, operation
      FROM  (
         SELECT my_function(ca.timeslice_id) AS x, agc.operation
         FROM   geometry_component agc
         JOIN   volume             av  ON av.id = agc.volume_id
         JOIN   volume_dependency  avd ON avd.id = av.contributor_id
         JOIN   my_rowset_function('2013-02-22') ca ON ca.feature_id = avd.id
         WHERE  agc.timeslice_id = 12345
         ORDER  BY agc.sequence
         ) sub1
      )sub2
   )sub3

Pass multiple sets or arrays of values to a function

You can achieve that with a simple SQL function. Key feature is the function generate_subscripts():

CREATE OR REPLACE FUNCTION f_attendance(_arr2d int[])
  RETURNS SETOF attendance AS
$func$
   SELECT a.*
   FROM   generate_subscripts($1, 1) i
   JOIN   attendance a ON a.class   = $1[i][1]
                      AND a.section = $1[i][2]
$func$  LANGUAGE ROWS 10 sql STABLE;

Call:

SELECT * FROM f_attendance(ARRAY[[1,1],[2,2]]);

Or the same with an array literal - which is more convenient in some contexts, especially with prepared statements:

SELECT * FROM f_attendance('{{1,1},{2,2}}');

The function always expects a 2D array. Even if you pass a single pair, nest it:

SELECT * FROM f_attendance('{{1,1}}');

Audit of your implementation

1. You made the function VOLATILE, but it can be STABLE. Per documentation:

   Because of this snapshotting behavior, a function containing only SELECT commands can safely be marked STABLE.

   Related:

   • How to pass a parameter into a date function

2. You also use LANGUAGE plpgsql instead of sql, which makes sense if you execute the function multiple times in the same session. But then you must also make it STABLE or you lose that potential performance benefit. The manual once more:

   STABLE and IMMUTABLE functions use a snapshot established as of the
   start of the calling query, whereas VOLATILE functions obtain a fresh
   snapshot at the start of each query they execute.

3. Your EXPLAIN output shows an Index Only Scan, not a sequential scan like you suspect in your comment.

4. There is also a sort step in your EXPLAIN output that does not match the code you show. Are you sure you copied the right EXPLAIN output? How did you obtain it anyway? PL/pgSQL functions are black boxes to EXPLAIN. Did you use auto_explain? Details:

   • Postgres query plan of a UDF invocation written in pgpsql

5. The Postgres query planner has no idea how many array elements the passed parameter will have, so it is hard to plan the query and it may default to a sequential scan (depending on more factors). You can help by declaring the expected number of rows. If you typically don't have more than 10 items add ROWS 10 like I did now above. And test again.

Return multiple values from array

The main function, allBy(), returns as soon as it sees the first matching artist. Try declaring an empty array and storing the matches you find in it so you can return the array outside the loop.

function allBy(artist) {

   var matches = []; // create an empty array to store our matches

   for ( let i = 0; i < collection.length; i++) {
       // console.log('current collection item: ', collection[i]); // optional: log or add a breakpoint here to understand what's happening on each iteration
       if (collection[i].artist === artist) {
         matches.push(collection[i].title); // add a match we've found
         // return [collection[i].title];
       }
   }

   return matches; // tada!
 }

JavaScript call same function name multiple times

Dont know if I really understand what you need, but the following example show you how to pass different arguments to the same function and call it many times you want.

let args = [{
  name: 'test',
  value: 'value1'
}, {
  name: 'test2',
  value: 'value2'
}, {
  name: 'test3',
  value: 'value3'
}];

function test(args) {
  if (args) //Check if function has arguments
  {
    let btn = document.createElement('button'); //create an element type button
    btn.innerText = args.name; //Set the created button text
    btn.value = args.value; //Set the created button value
    //Folowing code add 'EventListener' to catch the user 'click' action
    btn.addEventListener('click', function(e) {
      console.log('Button clicked with value: ' + this.value) //Log the value of the clicked button to verify if our function is working (set the name and value of generated button)
    });
    document.body.appendChild(btn); //Append the created button to the DOM
  } else {
    //Eles do something else
    console.log('no arguments passed')
  }
};

test() //Function test executed w/o arguments

//Create a loop on args, just for the example
for (i = 0; i < args.length; i++)
  test(args[i]) //Funciton test() executed with arguments