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