T-SQL stored procedure from SQL server to PostgreSQL

Question

For a bit of context: I've written a stored procedure that uses two tables, products (product_id, product_name, description, price, category_id) and supplies (shipment_id, suppliers_id, product_id (FK to products), supply_date, quantity).

The procedure takes a product_id and prints the description and certain info about the product's supply within the give time range (from date1 to date2).

This is the T-SQL code:

CREATE OR ALTER PROCEDURE productInfo
    (@product_id INT,
     @date1 DATE,
     @date2 DATE)
AS
BEGIN
    DECLARE @description VARCHAR(100),
            @shipment_id INT, 
            @supply_date DATE, 
            @quantity INT;

    DECLARE product_cursor CURSOR LOCAL FOR
        SELECT description 
        FROM products 
        WHERE product_id = @product_id

    OPEN product_cursor;

    FETCH NEXT FROM product_cursor INTO @description

    CLOSE product_cursor
    
    DECLARE product_supply_cursor CURSOR LOCAL FOR 
        SELECT shipment_id, supply_date, quantity
        FROM supplies
        WHERE product_id = @product_id

    OPEN product_supply_cursor;

    FETCH NEXT FROM product_supply_cursor INTO @shipment_id, @supply_date, @quantity;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @supply_date >= @date1 AND @supply_date <= @date2
        BEGIN
            PRINT 'DESCRIPTION: ' + @description
            PRINT 'SHIPMENT_ID: ' + STR(@shipment_id)
            PRINT 'SUPPLY_DATE: ' + convert(varchar, @supply_date, 23)
            PRINT 'QUANTITY: ' + STR(@quantity)
            PRINT ' '
        END;

        FETCH NEXT FROM product_supply_cursor INTO @shipment_id, @supply_date, @quantity;
    END;

    CLOSE product_supply_cursor;
END;

Obviously this procedure doesn't run in pgSQL and I have no experience on the matter so I'd like either a translation of this code or (if I'm asking too much) a hint about what things would have to change ( syntax or logic wise) to fit pgSQL language.

Start off with asking whether the T-SQL version makes sense anyway. Why would you `PRINT` instead of `SELECT` — Charlieface, Jan 17 '21 at 16:05

score 0 · Answer 1 · 2021-01-17T16:10:42.213

The SQL Server code is extremely convoluted to begin with. The use of cursors and the slow and inefficient (nested!) row-by-row processing can be simplified to a single SELECT statement that joins the two tables. Doing the range check on the two dates inside the cursor loop is extremely efficient. It is much better to apply that directly in the WHERE clause of the SELECT statement, so that you don't have to iterate over 10 million rows, just to show 5.

A naive migration would do something like this:

create procedure print_product_info(p_product_id int, p_date1 date, p_date2 date)
as
$$
declare
  l_row record;
begin
  for l_row in SELECT p.description, s.shipment_id, s.supply_date, s.quantity
               FROM supplies s
                 JOIN products p ON p.product_id = s.product_id
               WHERE p.product_id = p_product_id
                 AND s.supply_date >= p_date1
                 AND s.supply_date <= p_date2
  loop
    raise notice 'DESCRIPTION: %', l_row.description;
    raise notice 'SHIPMENT_ID: %', l_row.shipment_id;
    raise notice 'SUPPLY_DATE: %', to_char(l_row.supply_date, 'yyyy-mm-dd');
    raise notice 'QUANTITY: %', l_row.quantity;
  end loop;
end;
$$
language plpgsql;

However, this is not how one would do this in Postgres. Results should not be "printed", but simply "returned" by a function (procedures aren't intended to return things).

The proper way to implement this in Postgres is a set-returning function:

create function get_product_info(p_product_id int, p_date1 date, p_date2 date)
  returns table(description text, shipment_id int, supply_date date, quantity int)
as
$$
  SELECT p.description, s.shipment_id, s.supply_date, s.quantity
  FROM supplies s
   JOIN products p ON p.product_id = s.product_id
  WHERE p.product_id = p_product_id
   AND s.supply_date >= p_date1
   AND s.supply_date <= p_date2
$$
language sql;

To display the data, use:

select *
from get_product_info(42, date '2020-01-07', '2020-01-12');

Thanks for the help. I know it's not efficient but I need the use of cursors not for optimization but to show and highlight the knowledge of them. (It's for academic, nor business purpose) — BreakyBack, Jan 17 '21 at 16:12
A cursor based approach hardly ever makes sense. A set based approach is almost always the better choice (in terms of efficiency and scalability). — , Jan 17 '21 at 16:14
@BreakyBack: the usage of a cursor in PL/pgSQL is fully documented [in the manual](https://www.postgresql.org/docs/current/plpgsql-cursors.html) — , Jan 17 '21 at 16:29

T-SQL stored procedure from SQL server to PostgreSQL

1 Answers1