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 :
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)');