How to use variables Inside create view inside Procedure?

Question

I have a case where i want to create a view inside a procedure.

Works fine (Usage of variables)

CREATE OR REPLACE PROCEDURE createViewForAnalysis(analysis INT)
LANGUAGE plpgsql
AS
$$
BEGIN
 Perform
 code,
 isReport,
 description,
 SUM(dialoge) as dialoge,
 SUM(response_time) as response_time,
 SUM(response_time)/NULLIF(SUM(dialoge),0) as avg_response_time
 FROM codes_joined WHERE analyse_id=analysis
 GROUP BY code, isReport ,description;
END;
$$;

Also works fine (create view with fixed value in where)

CREATE OR REPLACE PROCEDURE createViewForAnalysis(analysis INT)
LANGUAGE plpgsql
AS
$$
BEGIN
 CREATE OR REPLACE VIEW tcodes_aggregated AS
 SELECT
 code,
 isReport,
 description,
 SUM(dialoge) as dialoge,
 SUM(response_time) as response_time,
 SUM(response_time)/NULLIF(SUM(dialoge),0) as avg_response_time
 FROM codes_joined AS pg WHERE analyse_id=1
 GROUP BY code, isReport ,description;
END;
$$;

When I want to use a variable inside a create view it doesn't take the value inside analysis. It throws "[42703] ERROR: column "analysis" does not exist"

CREATE OR REPLACE PROCEDURE createViewForAnalysis(analysis INT)
LANGUAGE plpgsql
AS
$$
BEGIN
 CREATE OR REPLACE VIEW tcodes_aggregated AS
 SELECT
 code,
 isReport,
 beschreibung,
 SUM(dialoge) as dialoge,
 SUM(response_time) as response_time,
 SUM(response_time)/NULLIF(SUM(dialoge),0) as avg_response_time
 FROM codes_joined AS pg WHERE analyse_id=analysis
 GROUP BY code, isReport ,description;
END;
$$;

Any hints?

• Bjarni Ragnarsson: I find this interesting. As I understand, the crux is, how to pass the variable into the create view statement with "WHERE analyse_id=1". Answers provide different approach to get similar results - but why can't the parameter be used as in "insert into table values (analysis)" for example?

Answer 1

Views can't have parameters or variables. I think what you want is a SQL function that returns the result of the query:

CREATE OR REPLACE function view_for_analysis(analysis INT)
 returns table (code integer, isreport boolean, beschreibung text, dialoge bigint, response_time bigint, avg_response_time numeric)
AS
$$
SELECT
 code,
 isReport,
 beschreibung,
 SUM(dialoge) as dialoge,
 SUM(response_time) as response_time,
 SUM(response_time)/NULLIF(SUM(dialoge),0) as avg_response_time
FROM codes_joined AS pg 
WHERE analyse_id = analysis
GROUP BY code, isReport ,description;
$$;
language SQL;

Note that I guessed the data types of the columns. You will have to adjust the returns table() part to the actual data types of your table.

Then you use it like this:

select *
from view_for_analysis(42);

Answer 2

Views can't have parameters, but you can create views dynamically using dynamic commands.

CREATE OR REPLACE PROCEDURE createViewForAnalysis(analysis INT)
LANGUAGE plpgsql
AS
$$
BEGIN
 EXECUTE 'CREATE OR REPLACE VIEW tcodes_aggregated AS
 SELECT
 code,
 isReport,
 beschreibung,
 SUM(dialoge) as dialoge,
 SUM(response_time) as response_time,
 SUM(response_time)/NULLIF(SUM(dialoge),0) as avg_response_time
 FROM codes_joined AS pg WHERE analyse_id=' || analysis ||
 'GROUP BY code, isReport, description;';
END;
$$;