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?

Stockman

You appear to be attempting to do dynamic SQL; take a look at PL/PgSQL's EXECUTE and the format parameter. See many related answers for details. – Detestation

Maybe it would be better to create VIEW as $BODY$ and make function select from this view? – Patrol

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.

Greenock

Use format with the %L format specifier instead of your concatenation and your array_to_string call. Cleaner. – Detestation

Answer 2

i'm not sure. but if you just want to pass array to dynamic sql string, you can mock it up?..

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;

$$
;

I your case would be like:

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

Coparcenary