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? — user330315
– user330315, Commented Nov 6, 2017 at 8: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
– JC Boggio, Commented Nov 6, 2017 at 8: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) — user330315
– user330315, Commented Nov 6, 2017 at 8:26

Vao Tsun · Accepted Answer · 2017-11-06 08:13:24Z

2

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)

answered Nov 6, 2017 at 8:13

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related