By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE master (master_id int PRIMARY KEY);
INSERT INTO master VALUES (1), (2);
CREATE TABLE other (
other_id int PRIMARY KEY,
master_id int NOT NULL REFERENCES master (master_id) ON DELETE RESTRICT
);
INSERT INTO other VALUES (1,1); -- only master_id 1 is referenced
-- Function --
CREATE OR REPLACE FUNCTION f_can_del(_tbl regclass, _col text, _id int)
RETURNS boolean
LANGUAGE plpgsql AS
$func$
DECLARE
_ct int; -- to receive count of deleted rows
BEGIN
EXECUTE format('DELETE FROM %s WHERE %I = $1', _tbl, _col)
USING _id; -- exception if other rows depend
GET DIAGNOSTICS _ct = ROW_COUNT;
IF _ct > 0 THEN
RAISE SQLSTATE 'MYERR'; -- If DELETE, raise custom exception
ELSE
RETURN NULL; -- ID not found, return NULL
END IF;
EXCEPTION
WHEN FOREIGN_KEY_VIOLATION THEN
RETURN FALSE;
CREATE TABLE
INSERT 0 2
CREATE TABLE
INSERT 0 1
CREATE FUNCTION
SELECT f_can_del('master', 'master_id', 1) AS test1
, f_can_del('master', 'master_id', 2) AS test2
, f_can_del('master', 'master_id', 23) AS test23;
| test1 | test2 | test23 |
|---|---|---|
| f | t | null |
SELECT 1
TABLE master;
| master_id |
|---|
| 1 |
| 2 |
SELECT 2