Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH byte AS (
	SELECT E'\\xDEADBEEF'::bytea as value
)
SELECT
	string_agg(get_bit(value, gs)::TEXT, '')
FROM 
	byte,
	generate_series(0, length(value) * 8 - 1) gs

string_agg

01111011101101010111110111110111

SELECT 1

SELECT E'\\xDEADBEEF'::bytea as value

value

\xdeadbeef

SELECT 1

WITH byte AS (
    SELECT E'\\xDEADBEEF'::bytea as value
)
SELECT
    *
FROM 
    byte

value

\xdeadbeef

SELECT 1

WITH byte AS (
    SELECT E'\\xDEADBEEF'::bytea as value
)
SELECT
    *
FROM 
    byte,
    length(value) AS count_bytes

value
count_bytes

\xdeadbeef
4

SELECT 1

WITH byte AS (
    SELECT E'\\xDEADBEEF'::bytea as value
)
SELECT
    *
FROM 
    byte,
    generate_series(0, length(value) - 1) gs

value
gs

\xdeadbeef
0

\xdeadbeef
1

\xdeadbeef
2

\xdeadbeef
3

SELECT 4

WITH byte AS (
    SELECT E'\\xDEADBEEF'::bytea as value
)
SELECT
    get_byte(value, gs)
FROM 
    byte,
    generate_series(0, length(value) - 1) gs

get_byte

222

173

190

239

SELECT 4

WITH byte AS (
    SELECT E'\\xDEADBEEF'::bytea as value
)
SELECT
    get_byte(value, gs)::bit(8)
FROM 
    byte,
    generate_series(0, length(value) - 1) gs

get_byte

11011110

10101101

10111110

11101111

SELECT 4

WITH byte AS (
    SELECT E'\\xDEADBEEF'::bytea as value
)
SELECT
    string_agg(get_byte(value, gs)::bit(8)::text, '')
FROM 
    byte,
    generate_series(0, length(value) - 1) gs

string_agg

11011110101011011011111011101111

SELECT 1

CREATE OR REPLACE FUNCTION to_bit(value bytea) RETURNS SETOF text AS 
$$
BEGIN
    RETURN QUERY 
        SELECT
            string_agg(get_byte(value, gs)::bit(8)::text, '')
        FROM 
            generate_series(0, length(value) - 1) gs;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION

SELECT to_bit(E'\\xDEADBEEF'::bytea)

to_bit

11011110101011011011111011101111

SELECT 1