Pigeo

-- This function behaves similarly to PostgreSQL's websearch_to_tsquery()
-- but it also accepts prefix matching operator :*
-- and it uses the standard tsquery operators <-> & | ! instead of websearch_to_tsquery()'s operators 'OR' and '-'
-- and it accepts parenthesis for grouping the operands
-- see: https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-PARSING-QUERIES

CREATE OR REPLACE FUNCTION websearch_to_tsquery_with_prefix_matching(config regconfig, query_text text)
RETURNS tsquery AS $$
DECLARE
    tsq text COLLATE "C"; -- string functions such as regexp_replace() and substring() requires a deterministic collation.

Pigeo

-- UUID to BYTEA:

SELECT DECODE(REPLACE(CAST(uuid_field AS TEXT),'-',''), 'hex') FROM table;

-- BYTEA to UUID:

SELECT CAST(ENCODE(bytea_field, 'hex') AS UUID) FROM table;

-- BONUS --