Variadic functions and composite types

Question

I have a function like so:

update_stuff_variadic(
 VARIADIC _stuff_array stuff[]
)

I understand that this syntax requires _stuff_array to match the column structure of the stuff[] table.

Instead of implicitly matching every column of the stuff table, is it possible to set the required type of the elements in the array?

Pseudo:

update_stuff_variadic(
 VARIADIC _stuff_array array['comment'::BIGINT, 'created_by'::TEXT]
)

This way the array passed to the function doesn't have to accommodate for columns such as serial id fields, or auto-populated fields such as timestamps.

Answer 1

You need a registered composite type (row type).
You register a composite type implicitly whenever you create a table or view.
You register a composite type explicitly with CREATE TYPE.

So you could:

CREATE TYPE my_partial_stuff AS (comment bigint, created_by text);
CREATE OR REPLACE FUNCTION update_stuff_variadic(
 VARIADIC _stuff_array my_partial_stuff[]
) ...

And then call the function with:

SELECT update_stuff_variadic ('(8,foo)', '(9,bar)');

• user973347: Fantastic. Thank you Erwin! :)

Answer 2

I just looked for the same topic and found out that this is working well in my 'PostgreSQL 14.4, compiled by Visual C++ build 1914, 64-bit':

CREATE OR REPLACE FUNCTION myfunc(
 VARIADIC in_ttravalues anyarray DEFAULT null::anyarray
)
 RETURNS json
 LANGUAGE 'plpgsql'
 COST 100
 VOLATILE PARALLEL UNSAFE
AS $BODY$
BEGIN
 -- do some stuff here
 return '{}'::json;
END
$BODY$

This allows to call:

SELECT * FROM myfunc( 'abc'::text, 123);