Can pl/pgsql function take a variable number of parameters?

Question

I have a database with multiple schemas which have the same tables and views, and I want to create a procedure that creates a view from one the views in the schema.

CREATE OR REPLACE FUNCTION create_summary_view(
 created_view character varying,
 common_view character varying,
 schema_1 character varying, 
 schema_2 character varying, 
 schema_3 character varying,
 ...
 schema_x character varying, 
)

create_view is the view that will be created, common_view is the view that is identical in all the schemas, and schema_x are the list of schemas whose common_views are being joined into created_view.

Does pl/pgsql have a syntax for indicating a variable number of parameters? created_view, common_view and at least one schema_x is required?

An alternative I'm considering it to pass a comma or space separated string of the schemas and use regexp_split_to_array to break it up, but it would be good to know of variable length arguments are possible.

Answer 1

Create a variadic function like this:

CREATE OR REPLACE FUNCTION create_summary_view(
 created_view text,
 common_view text,
 VARIADIC schemas text[]
) RETURNS text
 LANGUAGE sql AS
$$SELECT format('CREATE VIEW %I AS ', created_view) ||
 string_agg(
 format('SELECT * FROM %I.%I', schemas[i], common_view),
 ' UNION ALL '
 )
FROM generate_series(1, cardinality(schemas)) AS i$$;

Here is an invocation:

SELECT create_summary_view('newv', 'oldv', 's1', 's2', 's3');
 create_summary_view 
═══════════════════════════════════════════════════════════════════════════════════════════════════════════
 CREATE VIEW newv AS SELECT * FROM s1.oldv UNION ALL SELECT * FROM s2.oldv UNION ALL SELECT * FROM s3.oldv
(1 row)

• vfclists: Laurenz thanks for the additional example of the view