Passing the name of a view as a parameter to a function

Question

Can I pass the name of a view as parameter of a function? Example:

CREATE OR REPLACE FUNCTION example_test(test type_view) return void as $$
BEGIN
    start_ts = CLOCK_TIMESTAMP();
    REFRESH MATERIALIZED VIEW test;
    GET DIAGNOSTICS total_rows = ROW_COUNT;
    INSERT INTO control_dw_monitoring (name, start_time, end_time, total)
    VALUES ('view points that never contacted', start_ts, CLOCK_TIMESTAMP(), total_rows);
END
$$ language plpgsql;

You need dynamic SQL for this: http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN — , Feb 09 '15 at 12:27
[So do you have your answer?](http://meta.stackexchange.com/a/5235/169168) — Erwin Brandstetter, Jul 24 '15 at 03:21

score 1 · Answer 1 · answered Feb 09 '15 at 12:38

1

Try the following:

CREATE FUNCTION refresh_view_by_name(view_name text) RETURNS VOID AS $$
BEGIN
    EXECUTE 'REFRESH MATERIALIZED VIEW ' || view_name::regclass;
END
$$ LANGUAGE PLPGSQL;

answered Feb 09 '15 at 12:38

Antti Haapala -- Слава Україні

129,958
22
279
321

score 1 · Answer 2 · edited May 23 '17 at 11:58

The name of the view is an identifier and not a value. You cannot parametrize it, so you need dynamic SQL with EXECUTE - and you need to avoid SQL injection. Make out the parameter to be of type regclass to begin with.

CREATE OR REPLACE FUNCTION example_test(_v regclass)
  RETURNS VOID AS
$func$
DECLARE
   start_ts   timestamptz := clock_timestamp();
   total_rows int;
BEGIN
   EXECUTE 'REFRESH MATERIALIZED VIEW ' || _v; -- converted to text automatically
   GET DIAGNOSTICS total_rows = ROW_COUNT;

   INSERT INTO control_dw_monitoring (name, start_time, end_time, total)
   VALUES (_v::text, start_ts, clock_timestamp(), total_rows);
END
$func$ LANGUAGE plpgsql;

Fixed some other errors while being at it.
Related answer with more explanation:

Table name as a PostgreSQL function parameter

Passing the name of a view as a parameter to a function

2 Answers2