How to pass multiple rows to PostgreSQL function?

Question

How can we pass an array of (an unlimited amount of) rows (ie, a constant table) as the parameter/argument of a PostgreSQL function?

Here's an idea:

CREATE TYPE foo AS (
    x bigint,
    y smallint,
    z varchar(64)
);

CREATE OR REPLACE FUNCTION bar(bigint, foo[]) RETURNS TABLE(a bigint, x bigint, y smallint, z varchar(64)) AS
$$
    SELECT $1, x, y, z FROM unnest($2);
$$
LANGUAGE SQL;

The below function call works, but is there a way to make it shorter?

SELECT * FROM bar(1, ARRAY[(1,2,'body1'),(2,1,'body2')]::foo[]);

For example, we can't remove the ::foo[] cast, but is there a way to rewrite things so that we can omit it?

Should we be using a variatic argument?

Answer 1

My Google searches kept leading me here, so I'm going to post an answer that may not match exactly the needs of the OP, but might be helpful to others who see the title How to pass multiple rows to PostgreSQL function?

The OPs original request was for a type:

CREATE TYPE foo AS (
    x bigint,
    y smallint,
    z varchar(64)
);

If you are like me, you may want to pass in the results of a standard SELECT query to a function. So imagine I have a table (rather than a type) created as:

CREATE TABLE foo AS (
    x bigint,
    y smallint,
    z varchar(64)
);

I want to pass to a function the results of:

SELECT * from foo WHERE x = 12345;

The results may be zero or many rows.

According to the postgres docs at https://www.postgresql.org/docs/9.5/static/rowtypes.html creating a table also leads to the creation of a composite type with the same name. Which is helpful, since this automatically handles the CREATE TYPE foo in the original question, which I can now pass in to a function as an array.

Now I can create a function that accepts an array of foo typed values (simplified to focus on what is passed in, and how the records are used, rather than what is returned):

CREATE OR REPLACE FUNCTION bar(someint bigint, foos foo[]) RETURNS ...
LANGUAGE plpgsql
AS $$
DECLARE
    foo_record record;
begin

-- We are going to loop through each composite type value in the array
-- The elements of the composite value are referenced just like 
-- the columns in the original table row
FOREACH foo_record IN ARRAY foos LOOP
  -- do something, maybe like:
  INSERT INTO new_foo (
    x, y, z
  )
  VALUES (
    foo_record.x,
    foo_record.y,
    foo_record.z
  );

END LOOP;

RETURN...
END;
$$;

This function bar(bigint, foo[]) can then be called quite simply with:

SELECT bar(4126521, ARRAY(SELECT * from foo WHERE x = 12345));

which passes in all the rows of a query on the foo table as a foo typed array. The function as we have seen then performs some action against each of those rows.

Although the example is contrived, and perhaps not exactly what the OP was asking, it fits the title of the question and might save others from having to search more to find what they need.

EDIT naming the function arguments makes things easier