PostgreSQL version: PostgreSQL 14.8 on x86_64-pc-linux-musl, compiled by gcc (Alpine 12.2.1_git20220924-r10) 12.2.1 20220924, 64-bit
I’m trying to create a stored procedure which accepts two arguments (a and b is for the sake of simplicity, their actual names are _service_id and _car_id).
According to documentation on procedures in PostgreSQL 14, you can use arguments without any additional syntax in procedure’s body. Example provided by the documentation is as follows:
CREATE PROCEDURE insert_data(a integer, b integer)
LANGUAGE SQL
AS $$
INSERT INTO tbl VALUES (a);
INSERT INTO tbl VALUES (b);
$$;
However it is not a case, when I am trying to write my own procedure. Running the following code makes me stumble upon an error:
CREATE OR REPLACE PROCEDURE get_total_and_count_for_car_for_service (a int, b int)
LANGUAGE SQL
AS $$
CREATE OR REPLACE TEMPORARY VIEW total_and_count_for_car_for_service_result AS (
SELECT
SUM(
CASE WHEN cars.is_foreign THEN
services.cost_foreign
ELSE
services.cost_our
END) AS total,
COUNT(works.id)
FROM
works
LEFT JOIN cars ON cars.id = works.car_id
LEFT JOIN services ON services.id = works.service_id
WHERE
works.service_id = a
AND works.car_id = b
GROUP BY
works.car_id)
$$;
CALL get_total_and_count_for_car_for_service (2, 2);
SELECT * FROM total_and_count_for_car_for_service_result;
…and here goes an error:
car_service=# CALL get_total_and_count_for_car_for_service ( 2, 2);
ERROR: column "a" does not exist
LINE 1: ...es.id = works.service_id WHERE works.service_id = a AND work...
^
QUERY: CREATE OR REPLACE TEMPORARY VIEW total_and_count_for_car_for_service_result AS (SELECT SUM(CASE WHEN cars.is_foreign THEN services.cost_foreign ELSE services.cost_our END) AS total, COUNT(works.id) FROM works LEFT JOIN cars ON cars.id = works.car_id LEFT JOIN services ON services.id = works.service_id WHERE works.service_id = a AND works.car_id = b GROUP BY works.car_id)
CONTEXT: SQL function "get_total_and_count_for_car_for_service" statement 1
What’s going on and why my arguments are not visible to CREATE VIEW function, unlike in PostgreSQL documentation exmaple, where arguments are visible to INSERT? What would be a proper way to pass arguments in that case and make it work?
Note: I am aware that this stored procedure should be a function rather than a stored procedure and storing result in a temporary view is a crutch. However, in general, creation of views via procedures is not something strange, imho.
upd: db schema is created with following script
CREATE DATABASE car_service;
c car_service;
CREATE TABLE cars (
id SERIAL PRIMARY KEY,
num VARCHAR(20) NOT NULL,
color VARCHAR(20),
mark VARCHAR(20),
is_foreign BOOLEAN
);
CREATE TABLE masters (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE services (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
cost_our NUMERIC(18, 2),
cost_foreign NUMERIC(18, 2)
);
CREATE TABLE works (
id SERIAL PRIMARY KEY,
date_work DATE,
master_id INT REFERENCES masters(id),
car_id INT REFERENCES cars(id),
service_id INT REFERENCES services(id)
);
ALTER TABLE works
ADD CONSTRAINT fk_works_cars
FOREIGN KEY (car_id)
REFERENCES cars(id);
ALTER TABLE works
ADD CONSTRAINT fk_works_masters
FOREIGN KEY (master_id)
REFERENCES masters(id);
ALTER TABLE works
ADD CONSTRAINT fk_works_services
FOREIGN KEY (service_id)
REFERENCES services(id);
2
Answers
You cannot use parameters in a
CREATE VIEW
statement.Use dynamic SQL:
Just keep it simple, and use a function for this:
There is no (performance) benefit from creating a (temporary) view first. It’s slower and you have to use a database role that has enough permissions to create database objects. With this solution you don’t need pl/pgsql either, just pl/sql and that’s slightly faster as well.