Postgres function to pull the 'best available name' for any given room_id in the Synapse Homeserver db

gistfile1.txt

CREATE OR REPLACE FUNCTION pg_temp.name(TEXT) RETURNS TEXT AS $$
DECLARE
    rm_id TEXT;
    rm_name TEXT;
    rm_alias TEXT;
BEGIN
    rm_id := $1;
    rm_name := (
        SELECT name
        FROM matrix.room_names
        WHERE room_id = rm_id
    );
    rm_alias := (
        SELECT room_alias
        FROM room_aliases
        WHERE room_id = rm_id
        LIMIT 1
    );
    RETURN COALESCE(rm_name, rm_alias, rm_id);
END;
$$ LANGUAGE PLPGSQL;

You can add this to your .psqlrc file and your function will be ready for you to use as soon as you connect. As an aside, the idea of a .psqlrc file that runs a bunch of SQL as soon as you connect to the DB is absolutely terrifying :|