skip to Main Content

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


  1. You cannot use parameters in a CREATE VIEW statement.

    Use dynamic SQL:

    DECLARE
       sql text;
    BEGIN
       sql = format('CREATE TEMPORARY VIEW total_and_count_for_car_for_service_result ' ||
                    'AS (SELECT ... ' ||
                    'WHERE works.service_id = %s ' ||
                    '  AND works.car_id = $s ...',
                    a, b
             );
    
       EXECUTE sql;
    END;
    
    Login or Signup to reply.
  2. Just keep it simple, and use a function for this:

    CREATE OR REPLACE FUNCTION get_total_and_count_for_car_for_service(a INT, b INT)
        RETURNS TABLE
                (
                    CAR_ID INT,
                    TOTAL  NUMERIC,
                    COUNT  BIGINT
                )
        LANGUAGE sql
    AS
    $$
    SELECT works.car_id
         , 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 = $1
      AND works.car_id = $2
    GROUP BY works.car_id;
    $$;
    
    SELECT *
    FROM get_total_and_count_for_car_for_service(2, 2);
    

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search