How to create a VIEW from function with parameters?

Question

I have this function in PostgreSQL:

CREATE OR REPLACE FUNCTION func1(a integer, b timestamp, c integer[])
  RETURNS SETOF typ_new AS 
$BODY$
declare
begin
    CREATE OR REPLACE VIEW newView as (select * from func2($1,$2,$3));
end;
$BODY$
  LANGUAGE plpgsql VOLATILE

func2 also returns SETOF typ_new so they are compatible.

When running it I get an error : ERROR: there is no parameter $1 if I change the $1 to the parameter name a then the error changes to ERROR: column "a" does not exist

I also tried dynamic SQL:

    sqlstr ='CREATE OR REPLACE VIEW newView (columns... ) as
              (select * from func2('||$1||','||$2||','||$3||'))';
    execute sqlstr;

but it doesn't work because $3 is integer[] and || can't work with arrays.

How do I solve this?

Answer 1

CREATE OR REPLACE FUNCTION func1(a integer, b timestamp, c integer[]) RETURNS void AS 
$BODY$
BEGIN
  EXECUTE 'CREATE OR REPLACE VIEW newView AS ' ||
            'SELECT * FROM func2(' || $1 || ', ' || $2 || ', ' || array_to_string($3, ',') || ')';
  RETURN;
END;
$BODY$ LANGUAGE plpgsql STRICT;

Note that this function returns void, not SETOF typ_new, as you are creating a view, not returning data from the view.

Since func2() returns typ_new you do not have to explicitly declare the columns of the view, they will be taken from the SELECT statement: the elements of the typ_new type.