Inject aggregation function inside a procedure

Question

Is it possible to inject the name of a function (AVG, MAX...) as a procedure parameter?

CREATE PROCEDURE test(func TEXT)
LANGUAGE PLPGSQL
AS $$
BEGIN
DROP TABLE IF EXISTS foo;
CREATE TEMPORARY TABLE foo AS SELECT 
func(bar.column_a) as func_column_a
FROM bar;
END
$$;

Answer 1

You have to use dymnamc sql for that purpose

CREATE PROCEDURE test(func TEXT)
LANGUAGE PLPGSQL
AS $$
BEGIN
DROP TABLE IF EXISTS foo;
EXECUTE 'CREATE TEMPORARY TABLE foo AS SELECT' || func ||'(bar.column_a) as func_column_a
FROM bar;';
END
$$;

Answer 2

You need dynamic SQL as has been suggested.
But when passing anything but values (including object names like the function name in your example), you need to defend against SQL injection!

CREATE OR REPLACE PROCEDURE safe_proc(func text)
  LANGUAGE plpgsql AS
$proc$
BEGIN
   DROP TABLE IF EXISTS pg_temp.foo;  -- ② also safer
   EXECUTE format(                    -- ③
   'CREATE TEMP TABLE foo AS
    SELECT %I(bar.column_a) AS func_column_a FROM bar', $1); -- ① !!
END
$proc$;

① format() with the %I specifier double-quotes identifiers automatically where needed.
Else, a malicious user might do something like this:

CALL unsafe_proc(' 1; DELETE FROM bar; -- ');

Whoops! See what happens:

db<>fiddle here

Further reading:

• SQL injection in Postgres functions vs prepared queries
• Table name as a PostgreSQL function parameter

While being at it, you can also customize the result column name safely:

EXECUTE format('... SELECT %I(column_a) AS %I ...', $1, concat($1, _column_a);

② To be safe, also schema-qualify the temp table when dropping. Since you are not sure it exists, the first plain table of that name in the schema search path would get it. (The temporary schema is first in the search path by default.) See:

• How does the search_path influence identifier resolution and the “current schema”

③ Another side effect of using format(): It works with passing a NULL value. The result is that bar.column_a gets used as is. Parentheses around a single value ((bar.column_a)) are stripped as noise. You may or may not want to allow this as "no-op" function.
When concatenating with ||, the result of concatenating a NULL is NULL, and EXECUTE will raise an exception:

ERROR:  query string argument of EXECUTE is null

Unlike functions, procedures cannot be defined as STRICT.