src/madpack/diff_udoc.sql

------------------------------------------------------------------------------
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at

--   http://www.apache.org/licenses/LICENSE-2.0

-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
------------------------------------------------------------------------------

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 alter_schema(argstr text, schema_name text)
RETURNS text AS $$
    if argstr is None:
        return "NULL"
    return argstr.replace(schema_name + ".", 'schema_madlib.')
$$ LANGUAGE plpythonu;

CREATE OR REPLACE FUNCTION get_udocs(table_name text, schema_name text,
                                         type_filter text)
RETURNS VOID AS
$$
    import plpy

    plpy.execute("""
	CREATE TABLE {table_name} AS
	SELECT * FROM (
		SELECT index_method, opfamily_name,
			   array_to_string(array_agg(alter_schema(opfamily_operator::text, '{schema_name}')), ',')
			   	AS operators
		FROM (
			SELECT am.amname AS index_method, opf.opfname AS opfamily_name,
		       	   amop.amopopr::regoperator AS opfamily_operator
		    FROM pg_am am, pg_opfamily opf, pg_amop amop, pg_namespace n
		    WHERE opf.opfmethod = am.oid AND
		          amop.amopfamily = opf.oid AND
		          n.oid = opf.opfnamespace AND
		          n.nspname OPERATOR(pg_catalog.~) '^({schema_name})$'
		    ORDER BY index_method, opfamily_name, opfamily_operator
		    ) q
		GROUP BY (index_method, opfamily_name)
		) qq
	WHERE operators LIKE '%schema_madlib.{type_filter}%' OR '{type_filter}' LIKE 'Full'
	""".format(table_name=table_name, schema_name=schema_name, type_filter=type_filter))

$$ LANGUAGE plpythonu;

DROP TABLE if exists udoc_madlib_old_version;
DROP TABLE if exists udoc_madlib_new_version;

SELECT get_udocs('udoc_madlib_old_version','madlib_old_vers','Full');
SELECT get_udocs('udoc_madlib_new_version','madlib','Full');


SELECT old1.opfamily_name, old1.index_method
FROM udoc_madlib_old_version AS old1 LEFT JOIN udoc_madlib_new_version
	USING (index_method, opfamily_name, operators)
WHERE udoc_madlib_new_version.opfamily_name is NULL
ORDER BY 1;