کنترل با توابع PostgreSQL: بستن شکاف به عملکرد ORM

import 'dotenv/config';
import { Client } from 'pg';

const pg = new Client({
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT, 10),
  database: process.env.DB_NAME,
});

pg.connect().then(() => {
  console.log('db connected');
  pg.query(
    `INSERT INTO reservations (_date, hora, res_number, res_name, room, meal_plan, pax_number, cost, observations)
      VALUES
        ('2023-07-27', '21:00', 001, 'Jhon Doe', 'P01', 'SC', 2, 50.00, 'Sin observaciones')`,
  ).then((result) => {
    console.log(result);
    pg.end().then(() => {
      console.log('disconnected from db');
    });
  });
});

/*
db connected
Result {
  command: 'INSERT',
  rowCount: 1,
  oid: 0,
  rows: [],
  fields: [],
  ... more metada
}
disconnected from db
*/

prisma.pokemons.create({
    data:{
      id: 1100,
      name: 'new_pokemon'
    }
  }).then((result: any) => {
    console.log(result);
  })

/*
{ id: 1100, name: 'new_pokemon' }
*/

CREATE FUNCTION sum(num1 numeric, num2 numeric) -- declare function and parameteres
RETURNS numeric -- returned type
AS $$
BEGIN -- starting the code
  RETURN num1 + num2; -- result
END; -- end of code
$$ LANGUAGE plpgsql; -- end of the function

SELECT sum(2, 3); -- returns 5

CREATE TABLE reservations (
  id SERIAL PRIMARY KEY,
  pax INTEGER,
  capacity INTEGER,
  do_res DATE, -- date of reservation
  to_res TIME -- time of reservation
);

-- populating database with bad data that will throw exceptions in our function
INSERT INTO reservations (pax, capacity, do_res, to_res) VALUES 
(0, 0, '2022-01-01', '10:00:00');

/* Use it to play with the function if needed
-- populating database with good data
INSERT INTO reservations (pax, capacity, do_res, to_res) VALUES 
(2, 4, '2022-01-01', '10:00:00'),
(3, 6, '2022-01-02', '11:00:00'),
(4, 8, '2022-01-03', '12:00:00'),
(5, 10, '2022-01-04', '13:00:00'),
(6, 12, '2022-01-05', '14:00:00'),
(7, 14, '2022-01-06', '15:00:00'),
(8, 16, '2022-01-07', '16:00:00'),
(9, 18, '2022-01-08', '17:00:00'),
(10, 20, '2022-01-09', '18:00:00');
*/

SELECT SUM(pax)*100 / SUM(capacity) AS ocupation FROM reservations;

CREATE OR REPLACE FUNCTION porcentage_ocupation(fecha_i DATE, fecha_f DATE)
RETURNS FLOAT AS $$
DECLARE -- declaring variables
  total_pax INTEGER;
  total_capacity INTEGER;
  porcentage FLOAT;
BEGIN
  SELECT SUM(pax), SUM(capacity) INTO total_pax, total_capacity FROM reservations WHERE do_res BETWEEN fecha_i AND fecha_f;
  porcentage := total_pax * 100.0 / total_capacity;
  RETURN porcentage;
END;
$$ LANGUAGE plpgsql;

SELECT porcentage_ocupation('2022-01-01', '2022-12-31');
-- ERROR:  division by zero
-- CONTEXT:  PL/pgSQL function porcentaje_ocupation(date,date) line 8 at assignment

CREATE OR REPLACE FUNCTION porcentage_ocupation2(fecha_i DATE, fecha_f DATE)
RETURNS JSON AS $$ -- returning a JSON is a great option to return customized results
DECLARE
  total_pax INTEGER;
  total_capacity INTEGER;
  porcentage FLOAT;
  stack text; -- will get the stack of errors here
  result JSON; -- will build the result here
BEGIN -- begin the function
  BEGIN -- this second begin works as the try in a try/catch block
    SELECT SUM(pax), SUM(capacity) INTO total_pax, total_capacity FROM reservations WHERE do_res BETWEEN fecha_i AND fecha_f;
    porcentage := total_pax * 100.0 / total_capacity;
    result := json_build_object('isError', FALSE, 'result', porcentage);
  EXCEPTION -- and here is the catch in the try/catch block
    WHEN OTHERS THEN
      GET STACKED DIAGNOSTICS stack = PG_EXCEPTION_CONTEXT; -- how we can obtain the stack of the error in postgresql
      result := json_build_object('isError', TRUE, 'message', SQLERRM, 'errorCode', SQLSTATE, 'stack', stack); -- SQLERRM, SQLSTATE are system variables provided by postgresql
  END; -- end of the try/catch block
  RETURN result; -- returning the response
END;
$$ LANGUAGE plpgsql;

SELECT porcentage_ocupation2('2022-01-01','2022-12-31');
                                                                      porcentage_ocupation2                                                
----------------------
 {"isError" : true, "message" : "division by zero", "errorCode" : "22012", "stack" : "PL/pgSQL function porcentage_ocupation2(date,date) line 11 at assignment"}

pg.connect().then(() => {
  console.log('db connected');
  pg.query(`SELECT porcentage_ocupation2('2022-01-01','2022-12-31' as result)`).then(
    (result) => {
      console.log(result.rows[0].result); // looking for the result straight to the point it should be
      pg.end().then(() => {
        console.log('disconnected from db');
      });
    },
  );
});
/*
db connected
{
  isError: true,
  message: 'division by zero',
  errorCode: '22012',
  stack: 'PL/pgSQL function porcentage_ocupation2(date,date) line 11 at assignment'
}
disconnected from db
*/

CREATE OR REPLACE FUNCTION create_reservation(
  _pax INTEGER,
  _capacity INTEGER,
  _do_res DATE,
  _to_res TIME
) RETURNS JSON AS $$
DECLARE
  inserted_reservation reservations; -- here will retrived the inserted object
  response JSON;
  stack_info TEXT;
BEGIN
  BEGIN
    IF _do_res < CURRENT_DATE THEN
      response := json_build_object(
        'isError', FALSE, -- It could be considered an error (a bad request error)
        'message', 'Bad request: No record inserted - You cant create a reservation in the past',
        'rowsAffected', 0,
        'result', NULL
      );
    ELSE
      BEGIN
        INSERT INTO reservations (
          pax,
          capacity,
          do_res,
          to_res
        ) VALUES (
          _pax,
          _capacity,
          _do_res,
          _to_res
        )
        RETURNING * INTO inserted_reservation; -- retriving the insert object

        IF inserted_reservation IS NULL THEN
          response := json_build_object(
            'isError', TRUE, 'message', 'No record inserted',
            'rowsAffected', 0
            );
        ELSE
          response := json_build_object(
            'isError', FALSE, 'result', inserted_reservation, 'rowsAffected', 1
            );
        END IF;
      EXCEPTION
        WHEN OTHERS THEN
          GET STACKED DIAGNOSTICS stack_info = PG_EXCEPTION_CONTEXT;
          response := json_build_object(
            'isError', TRUE, 'message', SQLERRM, 'errorCode', SQLSTATE,
            'stack', stack_info
            );
      END;
    END IF;

    RETURN response;
  END;
END;
$$ LANGUAGE plpgsql;

SELECT create_reservation(
  2, 4, '2023-07-27', '19:00'
) AS result;