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

Question

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 ?

Why do you want a view that can only be used for a single query? Why not create a SQL function to which you pass the date range and that returns the result? — , Nov 06 '17 at 08:07
Because I thought a function returning 700000 rows would not be efficient but now that you make me think of it, it might not be that different from a view. Thanks for the idea, I will test this. — JC Boggio, Nov 06 '17 at 08:23
There is no difference in the memory needed for a `select * from view` and between the equivalent statement inside a SQL function (you don't need PL/pgSQL for that) — , Nov 06 '17 at 08:26

score 2 · Accepted Answer · answered Nov 06 '17 at 08:13

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)

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

1 Answers1