CODEWITHSUNDEEP
sqlpostgresqlplpgsql
JC Boggio
JC Boggio

Reputation: 407

Creating a view from a stored function using that function's parameters

I am trying to create a view like this :

CREATE OR REPLACE FUNCTION calculate(datemin DATE,datemax DATE) RETURNS VOID AS
$BODY$
BEGIN
    DROP VIEW IF EXISTS zdroits CASCADE;
    CREATE VIEW zdroits AS
    SELECT r.*
    FROM rights r
    WHERE r.date_paid BETWEEN datemin AND datemax;
    -- the rest of the function will use the view
END;
$BODY$ LANGUAGE plpgsql;

But PostgreSQL won't let me create the view, saying column datemin does not exist.

If instead, I replace the WHERE line with this, it is ok :

WHERE r.date_paid BETWEEN '2011-01-01' AND '2016-12-31'

I am not trying to create a parameterized view. I want to use the parameters passed to the function as "literals".

Trying to understand how this answer could help me, I experimented with things like this :

EXECUTE '
    CREATE VIEW zdroits AS
    SELECT r.*
    FROM rights r
    WHERE r.date_paid BETWEEN $1 AND $2;
' USING (datemin,datemax);

but the compiler says "there is no parameter $1".

Is there a way to do this ?

Upvotes: 0

Views: 42

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51496

I'm not asking why would you want that. as a_horse_with_no_name said - you can use function itself with parameters, but purely academically, you can create fn() like nelow:

t=# create or replace function mv(a int,b int) returns void as $$
begin
drop view if exists va;
execute format('create view va as select least(%s,%s)',$1,$2) using(a,b);
end;
$$ language plpgsql
;
CREATE FUNCTION
t=# select mv(2,4);
NOTICE:  view "va" does not exist, skipping
CONTEXT:  SQL statement "drop view if exists va"
PL/pgSQL function mv(integer,integer) line 3 at SQL statement
 mv
----

(1 row)

t=# select * from va;
 least
-------
     2
(1 row)

t=# select mv(7,4);
 mv
----

(1 row)

t=# select * from va;
 least
-------
     4
(1 row)

Upvotes: 2

Related Questions