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.

2 Answers 2

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
    Commented Oct 6, 2021 at 14:06
1

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

Your Answer

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

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