How to create a VIEW from a 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

func2also returns SETOF typ_newto be compatible.

When I start, I get an error: ERROR: there is no parameter $1 if I changed $1to the parameter name a, then the error will change 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 does not work, because there $3is integer[]and ||cannot work with arrays.

How to 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, because you are creating a view, not returning data from the view.

func2() typ_new, , SELECT: typ_new.

Answer 2

. sql-, ?..

do
$$
declare 
  a integer[];
begin
  a := array[11,22,33,22,11];
  raise info '%','you can mock up array in like this: array['||array_to_string(a,',')||']';

end;

$$
;

:

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