0

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.

vfclists
  • 1,093
  • 4
  • 14
  • 21

1 Answers1

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)
Laurenz Albe
  • 61,070
  • 4
  • 55
  • 90