src/madpack/diff_udt.sql

SET client_min_messages to ERROR;
\x on
CREATE OR REPLACE FUNCTION filter_schema(argstr text, schema_name text)
RETURNS text AS $$
    if argstr is None:
        return "NULL"
    return argstr.replace(schema_name + ".", '')
$$ LANGUAGE plpythonu;

CREATE OR REPLACE FUNCTION get_types(schema_name text)
RETURNS VOID AS
$$
    import plpy
    plpy.execute("""
        CREATE TABLE types_{schema_name} AS
        SELECT n.nspname as "schema",
               filter_schema(pg_catalog.format_type(t.oid, NULL), '{schema_name}') AS "name",
               t.typrelid AS "typrelid"
        FROM pg_catalog.pg_type t
          LEFT JOIN pg_catalog.pg_namespace n
          ON n.oid = t.typnamespace
        WHERE (t.typrelid = 0 OR
               (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid))
          --AND t.typname !~ '^_'
          AND n.nspname ~ '^({schema_name})$'
        ORDER BY 1, 2;
        """.format(schema_name=schema_name))
$$ LANGUAGE plpythonu;


CREATE OR REPLACE FUNCTION detect_changed_types(
    common_udt_table  text
)
RETURNS TEXT[] AS
$$
    import plpy
    OLD_SCHEMA = 'madlib_old_vers'
    NEW_SCHEMA = 'madlib'
    rv = plpy.execute("""
        SELECT name, old_relid, new_relid
        FROM {common_udt_table}
        """.format(common_udt_table=common_udt_table))
    changed_udt = []
    for r in rv:
        name = r['name']
        old_relid = r['old_relid']
        new_relid = r['new_relid']
        res = plpy.execute("""
            SELECT
                array_eq(old_type, new_type) AS changed
            FROM
            (
                SELECT array_agg(a.attname ||
                                 regexp_replace(pg_catalog.format_type(a.atttypid, a.atttypmod),
                                                '{old_schema}.', '') ||
                                 a.attnum order by a.attnum) AS old_type
                FROM pg_catalog.pg_attribute a
                WHERE a.attrelid = '{old_relid}' AND a.attnum > 0 AND NOT a.attisdropped
            ) t1,
            (
                SELECT array_agg(a.attname ||
                                 regexp_replace(pg_catalog.format_type(a.atttypid, a.atttypmod),
                                                '{new_schema}.', '') ||
                                 a.attnum order by a.attnum) AS new_type
                FROM pg_catalog.pg_attribute a
                WHERE a.attrelid = '{new_relid}' AND a.attnum > 0 AND NOT a.attisdropped
            ) t2
            """.format(old_relid=old_relid,
                       new_relid=new_relid,
                       old_schema=OLD_SCHEMA,
                       new_schema=NEW_SCHEMA))[0]['changed']
        if not res:
            changed_udt.append(name)
    return changed_udt
$$ LANGUAGE plpythonu;

-- Get UDTs
DROP TABLE IF EXISTS types_madlib;
DROP TABLE IF EXISTS types_madlib_old_vers;
set search_path = public, madlib;
SELECT get_types('madlib');
set search_path = public, madlib_old_vers;
SELECT get_types('madlib_old_vers');
set search_path = public;

--SELECT name FROM types_madlib;
--SELECT name FROM types_madlib_v15;

--Dropped
SELECT
    old_vers.name AS "Dropped UDTs"
FROM
    types_madlib_old_vers AS old_vers
    LEFT JOIN
    types_madlib AS new_vers
    USING (name)
WHERE new_vers.name IS NULL;

--Added
SELECT
     new_vers.name AS "Added UDTs"
FROM
     types_madlib_old_vers AS old_vers
     RIGHT JOIN
     types_madlib AS new_vers
     USING (name)
WHERE old_vers.name IS NULL;

--Common
DROP TABLE IF EXISTS types_common;
CREATE TABLE types_common AS
SELECT
    old_vers.name, old_vers.typrelid AS old_relid, new_vers.typrelid AS new_relid
FROM
    types_madlib_old_vers AS old_vers
    JOIN
    types_madlib AS new_vers
    USING (name)
WHERE old_vers.typrelid <> 0; -- 0 means base type

SELECT
    unnest(detect_changed_types('types_common')) AS "Changed UDTs";