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?.
-- Test setup --
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(_id int)
RETURNS boolean
LANGUAGE plpgsql AS
$func$
BEGIN
DELETE FROM master WHERE master_id = _id; -- Test-DELETE (will be rolled back)
IF NOT FOUND THEN
RETURN NULL; -- ID not found, return NULL
END IF;
RAISE SQLSTATE 'MYERR'; -- If DELETE, raise custom exception
EXCEPTION
WHEN FOREIGN_KEY_VIOLATION THEN
RETURN FALSE;
WHEN SQLSTATE 'MYERR' THEN
RETURN TRUE;
-- other exceptions are propagated as usual
END
$func$;
CREATE TABLE
INSERT 0 2
CREATE TABLE
INSERT 0 1
CREATE FUNCTION
SELECT f_can_del(1) AS test1
, f_can_del(2) AS test2
, f_can_del(23) AS test23;
| test1 | test2 | test23 |
|---|---|---|
| f | t | null |
SELECT 1
TABLE master;
| master_id |
|---|
| 1 |
| 2 |
SELECT 2