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?

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. — Craig Ringer
– Craig Ringer, Commented Jun 29, 2015 at 7:52
Maybe it would be better to create VIEW as $BODY$ and make function select from this view? — Smileek
– Smileek, Commented Jun 29, 2015 at 9:02

Patrick · Accepted Answer · 2015-06-29 09:19:21Z

9

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.

edited Jun 29, 2015 at 9:19

answered Jun 29, 2015 at 9:13

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Craig Ringer Over a year ago

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

Vao Tsun · Accepted Answer · 2015-06-29 09:13:12Z

0

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;

answered Jun 29, 2015 at 9:13

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

How to create a VIEW from function with parameters?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related