Variadic functions and composite types

Posted on

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

Leave a Reply

Your email address will not be published. Required fields are marked *