Postgres 13

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 function *once* per database
CREATE FUNCTION public.text(bytea, int, bool) 
  RETURNS text
  LANGUAGE plpgsql STABLE STRICT PARALLEL SAFE AS
$func$
BEGIN
   IF $3 THEN           -- true if the cast is an explicit cast, false otherwise.
     -- no infinite loop because we do the cast manually
     -- honors current setting for bytea_output, hence function not IMMUTABLE
      RETURN textin(byteaout($1));
   ELSE
      RAISE EXCEPTION 'Assignment cast from bytea to text forbidden by custom cast rules in this database!';
      RETURN textin(byteaout($1));  -- we should *never* get here!
   END IF;
END
$func$;
-- function wrappers to allow this in user-land
-- run as superuser (or dedicated daemon role)
CREATE FUNCTION public.f_create_cast_bytea2text() 
  RETURNS void
  LANGUAGE sql SECURITY DEFINER AS
'CREATE CAST (bytea AS text) WITH FUNCTION public.text(bytea, int, bool) AS ASSIGNMENT;';

CREATE FUNCTION public.f_drop_cast_bytea2text() 
  RETURNS void
  LANGUAGE sql SECURITY DEFINER AS
'DROP CAST IF EXISTS (bytea AS text);';
-- test table
CREATE TABLE test(id int, txt_col text, note text);
INSERT INTO test(id, txt_col, note) VALUES
  (-1, 'foo', 'plain text input')
, ( 0, '\000'::bytea, 'default bytea_output: ' || current_setting('bytea_output'));
2 rows affected

-- no exception
BEGIN;
SELECT public.f_create_cast_bytea2text();

SET LOCAL bytea_output = 'hex';
INSERT INTO test(id, txt_col, note)
VALUES (1, '\000'::bytea::text, 'local bytea_output: hex');    -- explicit cast still works!

SET LOCAL bytea_output = 'escape';
INSERT INTO test(id, txt_col, note)
VALUES (2, '\000'::bytea::text, 'local bytea_output: escape'); -- explicit cast still works

SELECT public.f_drop_cast_bytea2text();
END;
ERROR:  must be owner of type bytea or type text
CONTEXT:  SQL function "f_create_cast_bytea2text" statement 1


-- no exception
BEGIN;
SELECT public.f_drop_cast_bytea2text();

SELECT '\000'::bytea || text 'foo'; -- implicit cast still works

SELECT public.f_drop_cast_bytea2text();
END;
ERROR:  current transaction is aborted, commands ignored until end of transaction block


-- exception raised!
BEGIN;
SELECT public.f_create_cast_bytea2text();

INSERT INTO test(id, txt_col, note)
VALUES (3, '\000'::bytea, 'must fail!'); -- assignment cast forbidden!

SELECT public.f_drop_cast_bytea2text();
END;
ERROR:  current transaction is aborted, commands ignored until end of transaction block