doc: both SQL and PL/pgSQL to reverse bytea

[coolaj86]

• Using SQL (rather than PL/pgSQL) means that it can be inlined
• Using IMMUTABLE means that it can be optimized away when used on the same data
• Using PARALLEL SAFE indicates it can be used in many threads at once

SQL to reverse bytea

CREATE OR REPLACE FUNCTION bytea_reverse_series (bytea) RETURNS bytea
LANGUAGE SQL
IMMUTABLE
PARALLEL SAFE
RETURN (
  SELECT string_agg(substring($1 FROM byte_pos FOR 1), '')
  FROM generate_series(octet_length($1), 1, -1) AS byte_pos
);

PL/pgSQL to reverse bytea

CREATE OR REPLACE function bytea_reverse_loop (input_bytea bytea)
RETURNS bytea AS $$
DECLARE
    reversed_bytea bytea := repeat(E'\\000', length(input_bytea));
    byte_length int;
BEGIN
    byte_length := length(input_bytea);

    FOR i IN 1..byte_length LOOP
        reversed_bytea := set_byte(reversed_bytea, i - 1, get_byte(input_bytea, byte_length - i));
    END LOOP;

    RETURN reversed_bytea;
END;
$$ language plpgsql

License

The code snippets and documentation in this issue are licensed under the CC0-1.0 (Public Domain), as follows:

PostgreSQL Reverse BYTEA - reverse a byte array

Authored in 2024 by AJ ONeal [email protected]
To the extent possible under law, the author(s) have dedicated all copyright
and related and neighboring rights to this software to the public domain
worldwide. This software is distributed without any warranty.

You should have received a copy of the CC0 Public Domain Dedication along with
this software. If not, see https://creativecommons.org/publicdomain/zero/1.0/