Stored Procedures

CREATE PROCEDURE procedure_name(parameters)
  LANGUAGE procedure_language
  AS procedure_body

$ cockroach demo

CREATE OR REPLACE PROCEDURE delete_earliest_histories (
	num_deletions INT, remaining_histories REFCURSOR
)
LANGUAGE PLpgSQL
AS $$
DECLARE
	counter INT := 0;
	deleted_timestamp TIMESTAMP;
	deleted_ride_id UUID;
	latest_timestamp TIMESTAMP;
BEGIN
	-- Raise an exception if the table has fewer rows than the number to delete
	IF (SELECT COUNT(*) FROM vehicle_location_histories) < num_deletions THEN
	    RAISE EXCEPTION 'Only % row(s) in vehicle_location_histories',
	    (SELECT count(*) FROM vehicle_location_histories)::STRING;
	END IF;

	-- Delete 1 row with each loop iteration, and report its timestamp and ride ID
	WHILE counter < num_deletions LOOP
		DELETE FROM vehicle_location_histories
		WHERE timestamp IN (
			SELECT timestamp FROM vehicle_location_histories
			ORDER BY timestamp
			LIMIT 1
		)
		RETURNING ride_id, timestamp INTO deleted_ride_id, deleted_timestamp;

		-- Report each row deleted
		RAISE NOTICE 'Deleted ride % with timestamp %', deleted_ride_id, deleted_timestamp;

		counter := counter + 1;
	END LOOP;

	-- Open a cursor for the remaining rows in the table
	OPEN remaining_histories FOR SELECT * FROM vehicle_location_histories ORDER BY timestamp;
END;
$$;

BEGIN;

CALL delete_earliest_histories (5, 'rides_left');

NOTICE: Deleted ride 0a3d70a3-d70a-4d80-8000-000000000014 with timestamp 2019-01-02 03:04:05
NOTICE: Deleted ride 0b439581-0624-4d00-8000-000000000016 with timestamp 2019-01-02 03:04:05.001
NOTICE: Deleted ride 09ba5e35-3f7c-4d80-8000-000000000013 with timestamp 2019-01-02 03:04:05.002
NOTICE: Deleted ride 0fdf3b64-5a1c-4c00-8000-00000000001f with timestamp 2019-01-02 03:04:05.003
NOTICE: Deleted ride 049ba5e3-53f7-4ec0-8000-000000000009 with timestamp 2019-01-02 03:04:05.004
CALL

FETCH 3 from rides_left;

    city   |               ride_id                |        timestamp        | lat | long
-----------+--------------------------------------+-------------------------+-----+-------
  new york | 0c49ba5e-353f-4d00-8000-000000000018 | 2019-01-02 03:04:05.005 |  -88 |  -83
  new york | 0083126e-978d-4fe0-8000-000000000001 | 2019-01-02 03:04:05.006 |  170 |  -16
  new york | 049ba5e3-53f7-4ec0-8000-000000000009 | 2019-01-02 03:04:05.007 | -149 |   63

CREATE OR REPLACE PROCEDURE delete_earliest_histories (
	num_deletions INT, remaining_histories REFCURSOR
  )

LANGUAGE PLpgSQL

DECLARE
	counter INT := 0;
	deleted_timestamp TIMESTAMP;
	deleted_ride_id UUID;
	latest_timestamp TIMESTAMP;

BEGIN
  ...
  END

IF (SELECT COUNT(*) FROM vehicle_location_histories) < num_deletions THEN
	RAISE EXCEPTION 'Only % row(s) in vehicle_location_histories', (SELECT count(*) FROM vehicle_location_histories)::STRING;
  END IF;

WHILE counter < num_deletions LOOP
	DELETE FROM vehicle_location_histories
	WHERE timestamp IN (
  	SELECT timestamp FROM vehicle_location_histories
  	ORDER BY timestamp
  	LIMIT 1
	)
	RETURNING ride_id, timestamp INTO deleted_ride_id, deleted_timestamp;
	RAISE NOTICE 'Deleted ride % with timestamp %', deleted_ride_id, deleted_timestamp;
	counter := counter + 1;
  END LOOP;

OPEN remaining_histories FOR SELECT * FROM vehicle_location_histories ORDER BY timestamp;

SET CLUSTER SETTING sql.procedures.plpgsql.late_binding.enabled = true;

CREATE PROCEDURE create_audit_events()
LANGUAGE PLpgSQL
AS $$
BEGIN
  CREATE TABLE IF NOT EXISTS audit_events (
    event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_type STRING NOT NULL,
    event_timestamp TIMESTAMPTZ NOT NULL DEFAULT now()
  );
  INSERT INTO audit_events (event_type) VALUES ('procedure called');
END;
$$;

CALL create_audit_events();

ALTER PROCEDURE delete_earliest_histories RENAME TO delete_histories;