1

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.

1 Answer 1

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)');
1
  • Fantastic. Thank you Erwin! :)
    – user973347
    Oct 6, 2021 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.