How to create a view in sql that takes parameters

Question

I want to create a view that takes parameters and then make a selection on this view like this

CREATE OR REPLACE FUNCTION statistiquess(dateDeb date, dateFin date)
RETURNS void AS
$$
 CREATE OR REPLACE VIEW statistics 
 AS 
 SELECT 
 e.matricule_ens, 
 e.nom_ens, 
 e.prenom_ens, 
 m.code_matiere, 
 m.nom_matiere, 
 f.id_formation, 
 f.nom_formation, 
 SUM ((CAST (c.heure_fin AS TIME))-(CAST (c.heure_deb AS TIME))) AS heure_total_programme, 
 SUM ((CAST (c.heure_dep_ens AS TIME))-(CAST (c.heure_arr_ens AS TIME))) AS heure_total_enseigne 
 FROM 
 enseignant e inner join cours c on e.matricule_ens = c.matricule_ens 
 inner join matiere m on c.code_matiere = m.code_matiere 
 inner join formation f on f.id_formation = c.id_formation 
 WHERE
 c.jour between dateDeb and dateFin
 GROUP BY 
 e.matricule_ens, m.code_matiere, f.id_formation 
 ORDER BY 
 e.nom_ens;
$$
LANGUAGE SQL;

I have this error when i try to select all from function like this

select * from statistiquess('2019-03-06', '2019-03-29');

ERREUR: la colonne « datedeb » n'existe pas

LINE 6: ..._formation = c.id_formation where (c.jour between datedeb an...
QUERY: 
 CREATE OR REPLACE VIEW statistics AS select e.matricule_ens, e.nom_ens, e.prenom_ens, m.code_matiere, m.nom_matiere, f.id_formation, f.nom_formation, 
 SUM ((CAST (c.heure_fin AS TIME))-(CAST (c.heure_deb AS TIME))) AS heure_total_programme, 
 SUM ((CAST (c.heure_dep_ens AS TIME))-(CAST (c.heure_arr_ens AS TIME))) AS heure_total_enseigne 
 from enseignant e inner join cours c on e.matricule_ens = c.matricule_ens inner join matiere m on c.code_matiere = m.code_matiere 
 inner join formation f on f.id_formation = c.id_formation where (c.jour between datedeb and datefin) 
 GROUP BY e.matricule_ens, m.code_matiere, f.id_formation ORDER BY e.nom_ens;

Answer 1

the syntax you have above has function not a view like as the title says. only functions and stored procedures can take parameters. function return a value and stored procedures don't. the syntax for a function is

CREATE [OR REPLACE] FUNCTION function_name (arguments) 
RETURNS return_datatype AS $variable_name$
 DECLARE
 declaration;
 [...]
 BEGIN
 < function_body >
 [...]
 RETURN { variable_name | value }
 END; LANGUAGE plpgsql;

the syntax for a stored procedure

CREATE [OR REPLACE] PROCEDURE procedure_name(parameter_list)
LANGUAGE language_name
AS $$
 stored_procedure_body;
$$;

a view is a stored query that you can query like any other table.

SELECT * FROM statistiquess WHERE c.jour BETWEEN '2019-03-06' AND '2019-03-29'; 

without understanding your data and use case it's hard to say which one you should use. from your question it sounds like you either need a view or stored procedure.

• Catherine: all my data are in the course table which contains the start time of the course, the end time of the course, the time of arrival of the teacher, the departure time of the teacher, the identifier of the teacher, the identifier of the subject and the identifier of the training. so I want to calculate the total number of hours programmed, the total number of hours taught, the total number of hours missed, group by subject, teacher, training between two dates
• denov: you could create a view that has 1 row per event and the query that. or you could create a stored procedure that outputs just what you need. the view will allow you to ask different questions on the same data set.

Answer 2

You don't need to create a view inside the function to do that. Just return the result of the query from within the function:

CREATE OR REPLACE FUNCTION statistiquess(dateDeb date, dateFin date)
RETURNS tables (matricule_ens text, nom_ens text, prenom_ens text, 
 code_matiere text, nom_matiere text, id_formation int,
 nom_formation text, heure_total_programme bigint, 
 heure_total_enseigne bigtin) AS
$$
 SELECT 
 e.matricule_ens, 
 e.nom_ens, 
 e.prenom_ens, 
 m.code_matiere, 
 m.nom_matiere, 
 f.id_formation, 
 f.nom_formation, 
 SUM ((CAST (c.heure_fin AS TIME))-(CAST (c.heure_deb AS TIME))) AS heure_total_programme, 
 SUM ((CAST (c.heure_dep_ens AS TIME))-(CAST (c.heure_arr_ens AS TIME))) AS heure_total_enseigne 
 FROM 
 enseignant e inner join cours c on e.matricule_ens = c.matricule_ens 
 inner join matiere m on c.code_matiere = m.code_matiere 
 inner join formation f on f.id_formation = c.id_formation 
 WHERE
 c.jour between dateDeb and dateFin
 GROUP BY 
 e.matricule_ens, m.code_matiere, f.id_formation 
 ORDER BY 
 e.nom_ens;
$$
LANGUAGE SQL;

You will have to adjust the data types of the returned columns (inside the table (...) part) - I only guessed what they could be.

Then you can use it like a "view with parameters":

select * 
from statistiquess('2019-03-06', '2019-03-29');

• denov: wouldn't a stored procedure be better if you're just going to return a table?
• user330315: @denov: no, a (SQL) function is much better. For one, you can join against the result of a function and you can apply where conditions on it (not possible with a procedure) Secondly the optimizer can inline the query if the function is used in a bigger contents (e.g. a join). And additionally: a procedure can only "return" something if used with a refcursor as an out parameter. Very complicated to use when running in a SQL client