CODEWITHSUNDEEP
sqlpostgresqldynamic-sql
John
John

Reputation: 1852

How to create a VIEW from function with parameters?

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?

Upvotes: 7

Views: 13705

Answers (2)

Patrick
Patrick

Reputation: 32199

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.

Upvotes: 9

Vao Tsun
Vao Tsun

Reputation: 51466

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;

Upvotes: 0

Related Questions