Working with Stored Procedures

CREATE PROCEDURE archive_old_orders(cutoff date)
LANGUAGE plpgsql AS $
BEGIN
   INSERT INTO orders_archive SELECT * FROM orders WHERE placed_at < cutoff;
   DELETE FROM orders WHERE placed_at < cutoff;
   COMMIT;
END $;

CALL archive_old_orders('2024-01-01');

CREATE PROCEDURE batch_process() LANGUAGE plpgsql AS $
BEGIN
   LOOP
      WITH batch AS (SELECT id FROM queue LIMIT 1000 FOR UPDATE SKIP LOCKED)
      DELETE FROM queue WHERE id IN (SELECT id FROM batch);
      EXIT WHEN NOT FOUND;
      COMMIT;
   END LOOP;
END $;

CREATE PROCEDURE inc_count(INOUT cnt int) LANGUAGE plpgsql AS $
BEGIN cnt := cnt + 1; END $;
CALL inc_count(5);   -- returns 6

BEGIN
   ...
EXCEPTION WHEN OTHERS THEN
   ROLLBACK;
   RAISE;
END;

CREATE OR REPLACE PROCEDURE archive_old_orders(cutoff date) ...

\df+ schema.*
SELECT proname FROM pg_proc WHERE prokind = 'p';   -- procedures only

DROP PROCEDURE IF EXISTS archive_old_orders(date);