/* contrib/dump_stat/dump_stat--1.0--1.1.sql */

-- complain if script is sourced in psql, rather than via ALTER EXTENSION
\echo Use "ALTER EXTENSION dump_stat UPDATE TO '1.1'" to load this file. \quit


CREATE OR REPLACE FUNCTION @extschema@.anyarray_elemtype(arr anyarray) RETURNS oid
AS 'MODULE_PATHNAME', 'anyarray_elemtype'
LANGUAGE C STRICT IMMUTABLE;


CREATE FUNCTION @extschema@.anyarray_to_text(arr anyarray) RETURNS TEXT AS $$
	select format('array_in(%s, %s::regtype, -1)', quote_literal(arr::text),
				  quote_literal(@extschema@.to_schema_qualified_type(@extschema@.anyarray_elemtype(arr::anyarray))));
$$ LANGUAGE sql STRICT IMMUTABLE;


CREATE OR REPLACE FUNCTION @extschema@.to_schema_qualified_operator(opid oid) RETURNS TEXT AS $$
	DECLARE
		r		record;
		result	text;
		ltype	text;
		rtype	text;

	BEGIN
		if opid = 0 then
			return '0';
		end if;

		select nspname, oprname, oprleft, oprright
		from pg_catalog.pg_operator inner join pg_catalog.pg_namespace
				on oprnamespace = pg_namespace.oid
		where pg_operator.oid = opid
		into r;

		if r is null then
			raise exception '% is not a valid operator id', opid;
		end if;

		if r.oprleft = 0 then
			ltype := 'NONE';
		else
			ltype := @extschema@.to_schema_qualified_type(r.oprleft::oid);
		end if;

		if r.oprright = 0 then
			rtype := 'NONE';
		else
			rtype := @extschema@.to_schema_qualified_type(r.oprright::oid);
		end if;

		return format('%s.%s(%s, %s)',
					  quote_ident(r.nspname::text), r.oprname::text,
					  ltype, rtype);
	END;
$$ LANGUAGE plpgsql STRICT;


CREATE OR REPLACE FUNCTION @extschema@.to_schema_qualified_type(typid oid) RETURNS TEXT AS $$
	DECLARE
		result text;

	BEGIN
		select quote_ident(nspname::text) || '.' || quote_ident(typname::text)
		from pg_catalog.pg_type inner join pg_catalog.pg_namespace
				on typnamespace = pg_namespace.oid
		where pg_type.oid = typid
		into result;

		if result is null then
			raise exception '% is not a valid type id', typid;
		end if;

		return result;
	END;
$$ LANGUAGE plpgsql STRICT;


CREATE OR REPLACE FUNCTION @extschema@.to_schema_qualified_relation(reloid oid) RETURNS TEXT AS $$
	DECLARE
		result text;

	BEGIN
		select quote_ident(nspname::text) || '.' || quote_ident(relname::text)
		from pg_catalog.pg_class inner join pg_catalog.pg_namespace
				on relnamespace = pg_namespace.oid
		where pg_class.oid = reloid
		into result;

		if result is null then
			raise exception '% is not a valid relation id', reloid;
		end if;

		return result;
	END;
$$ LANGUAGE plpgsql STRICT;


CREATE FUNCTION @extschema@.to_attname(relation regclass, colnum int2) RETURNS TEXT AS $$
	DECLARE
		result text;

    BEGIN
		select attname
		from pg_catalog.pg_attribute
		where attrelid = relation and attnum = colnum
		into result;

		if result is null then
			raise exception 'attribute #% of relation % not found',
							colnum, quote_literal(relation::text);
		end if;

		return result;
	END;
$$ LANGUAGE plpgsql STRICT;


CREATE FUNCTION @extschema@.to_attnum(relation regclass, col text) RETURNS INT2 AS $$
	DECLARE
		result int2;

    BEGIN
		select attnum
		from pg_catalog.pg_attribute
		where attrelid = relation and attname = col
		into result;

		if result is null then
			raise exception 'attribute % of relation % not found',
							quote_literal(col), quote_literal(relation::text);
		end if;

		return result;
	END;
$$ LANGUAGE plpgsql STRICT;


CREATE FUNCTION @extschema@.to_atttype(relation regclass, col text) RETURNS TEXT AS $$
	DECLARE
		result text;

    BEGIN
		select @extschema@.to_schema_qualified_type(atttypid::oid)
		from pg_catalog.pg_attribute
		where attrelid = relation and attname = col
		into result;

		if result is null then
			raise exception 'attribute % of relation % not found',
							quote_literal(col), quote_literal(relation::text);
		end if;

		return result;
	END;
$$ LANGUAGE plpgsql STRICT;


CREATE FUNCTION @extschema@.to_atttype(relation regclass, colnum int2) RETURNS TEXT AS $$
	DECLARE
		result text;

    BEGIN
		select @extschema@.to_schema_qualified_type(atttypid::oid)
		from pg_catalog.pg_attribute
		where attrelid = relation and attnum = colnum
		into result;

		if result is null then
			raise exception 'attribute #% of relation % not found',
							colnum, quote_literal(relation::text);
		end if;

		return result;
	END;
$$ LANGUAGE plpgsql STRICT;


CREATE OR REPLACE FUNCTION @extschema@.to_namespace(nsp text) RETURNS OID AS $$
	DECLARE
		result oid;

    BEGIN
		select oid
		from pg_catalog.pg_namespace
		where nspname = nsp
		into result;

		if result is null then
			raise exception 'schema % does not exist',
							quote_literal(nsp);
		end if;

		return result;
	END;
$$ LANGUAGE plpgsql STRICT;


CREATE OR REPLACE FUNCTION @extschema@.get_namespace(relation oid) RETURNS OID AS $$
	DECLARE
		result oid;

    BEGIN
		select relnamespace
		from pg_catalog.pg_class
		where oid = relation
		into result;

		if result is null then
			raise exception 'relation % does not exist', relation;
		end if;

		return result;
	END;
$$ LANGUAGE plpgsql STRICT;




CREATE  FUNCTION @extschema@.dump_statistic(relation regclass) RETURNS SETOF TEXT AS $$
	DECLARE
		cmd		text;		-- main query
		in_args	text;		-- args for insert
		up_args	text;		-- args for upsert
		pk_args	text[];		-- helps identify row
		c_args	text[];		-- complete args for cmd

		fstaop	text := '%s::regoperator';
		fstaatt	text := '@extschema@.to_attnum(%s, %s)';

		stacols text[] = array['stakind',
							   'staop',
							   'stanumbers',
							   'stavalues'];

		r		record;
		ncols	int := 26;	-- number of columns in pg_statistic


		relname	text;		-- quoted relation name
		attname	text;		-- quoted attribute name
		atttype text;		-- quoted attribute type

	BEGIN
		for r in
			select * from pg_catalog.pg_statistic
			where starelid = relation
				and @extschema@.get_namespace(starelid) != @extschema@.to_namespace('information_schema')
				and @extschema@.get_namespace(starelid) != @extschema@.to_namespace('pg_catalog') loop

			relname := @extschema@.to_schema_qualified_relation(r.starelid::oid);
			attname := quote_literal(@extschema@.to_attname(relname::regclass, r.staattnum::int2));
			atttype := quote_literal(@extschema@.to_atttype(relname::regclass, r.staattnum::int2));
			relname := quote_literal(relname); -- redefine relname

			-- args: relname, up_args, in_args
			cmd := 'WITH upsert as ( ' ||
						'UPDATE pg_catalog.pg_statistic SET %2$s ' ||
						'WHERE starelid = ' || format('%1$s::regclass', relname) || ' '
							'AND @extschema@.to_attname(' || relname || '::regclass, staattnum::int2) = ' || attname || ' '
							'AND @extschema@.to_atttype(' || relname || '::regclass, staattnum::int2) = ' || atttype || ' '
							'AND stainherit = ' || r.stainherit || ' ' ||
						'RETURNING *), ' ||
				   'ins as ( ' ||
						'SELECT %3$s ' ||
						'WHERE NOT EXISTS (SELECT * FROM upsert) '
							'AND @extschema@.to_attnum (' || relname || '::regclass, ' || attname || ') IS NOT NULL '
							'AND @extschema@.to_atttype(' || relname || '::regclass, ' || attname || ') = ' || atttype ||
						') ' ||
				   'INSERT INTO pg_catalog.pg_statistic SELECT * FROM ins;';

			pk_args := array [
				-- identity
				relname				|| '::regclass',	-- table
				format(fstaatt, relname, attname),		-- column
				r.stainherit::text	|| '::bool'			-- + children?
			];

			c_args := array [
				-- specific
				r.stanullfrac	|| '::real',
				r.stawidth		|| '::integer',
				r.stadistinct	|| '::real',

				-- stakind
				r.stakind1::text,
				r.stakind2::text,
				r.stakind3::text,
				r.stakind4::text,
				r.stakind5::text,

				-- staop
				format(fstaop, quote_literal(@extschema@.to_schema_qualified_operator(r.staop1::oid))),
				format(fstaop, quote_literal(@extschema@.to_schema_qualified_operator(r.staop2::oid))),
				format(fstaop, quote_literal(@extschema@.to_schema_qualified_operator(r.staop3::oid))),
				format(fstaop, quote_literal(@extschema@.to_schema_qualified_operator(r.staop4::oid))),
				format(fstaop, quote_literal(@extschema@.to_schema_qualified_operator(r.staop5::oid))),

				-- stanumbers
				coalesce(quote_literal(r.stanumbers1::text), 'NULL') || '::real[]',
				coalesce(quote_literal(r.stanumbers2::text), 'NULL') || '::real[]',
				coalesce(quote_literal(r.stanumbers3::text), 'NULL') || '::real[]',
				coalesce(quote_literal(r.stanumbers4::text), 'NULL') || '::real[]',
				coalesce(quote_literal(r.stanumbers5::text), 'NULL') || '::real[]',

				-- stavalues
				coalesce(@extschema@.anyarray_to_text(r.stavalues1::anyarray), 'NULL') || '::anyarray',
				coalesce(@extschema@.anyarray_to_text(r.stavalues2::anyarray), 'NULL') || '::anyarray',
				coalesce(@extschema@.anyarray_to_text(r.stavalues3::anyarray), 'NULL') || '::anyarray',
				coalesce(@extschema@.anyarray_to_text(r.stavalues4::anyarray), 'NULL') || '::anyarray',
				coalesce(@extschema@.anyarray_to_text(r.stavalues5::anyarray), 'NULL') || '::anyarray'
			];

			assert array_length(pk_args || c_args, 1) = ncols;

			-- format string: %s, %s, %s, %s...
			select string_agg('%s', ', ') from generate_series(1, ncols) into in_args;

			-- format string: stakind1 = %s, ..., stavalues5 = %s
			select string_agg(n || j || ' = %s', ', ' order by i, j)
			from unnest(stacols) with ordinality as a(n, i),
				 generate_series(1, 5) as j
			into up_args;

			-- prepend missing columns that might change
			up_args := 'stanullfrac = %s, stawidth = %s, stadistinct = %s, ' || up_args;

			-- build final query
			return next format(cmd, relname,
							   format(up_args, variadic c_args),
							   format(in_args, variadic pk_args || c_args));
		end loop;

		return;
	END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION @extschema@.dump_statistic(schema_name text, table_name text) RETURNS SETOF TEXT AS $$
	DECLARE
		relation	text;
		i			text;

	BEGIN
		relation := quote_ident(schema_name) || '.' || quote_ident(table_name);

		for i in select @extschema@.dump_statistic(relation::regclass) loop
			return next i;
		end loop;

		return;

	EXCEPTION
		when invalid_schema_name then
			raise exception 'schema % does not exist',
							quote_literal(schema_name);
		when undefined_table then
			raise exception 'relation % does not exist',
							quote_literal(relation);
	END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION @extschema@.dump_statistic(schema_name text) RETURNS SETOF TEXT AS $$
	DECLARE
		relid	oid;
		i		text;

	BEGIN
		-- validate schema name
		perform @extschema@.to_namespace(schema_name);

		for relid in
				select pg_class.oid
				from pg_catalog.pg_namespace
					inner join pg_catalog.pg_class
					on relnamespace = pg_namespace.oid
				where nspname = schema_name loop

			for i in select @extschema@.dump_statistic(relid::regclass) loop
				return next i;
			end loop;
		end loop;

		return;

	EXCEPTION
		when invalid_schema_name then
			raise exception 'schema % does not exist',
							quote_literal(schema_name);
	END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION @extschema@.dump_statistic() RETURNS SETOF TEXT AS $$
	DECLARE
		relid	oid;
		i		text;

	BEGIN
		for relid in
				select pg_class.oid
				from pg_catalog.pg_namespace
					inner join pg_catalog.pg_class
					on relnamespace = pg_namespace.oid loop

			for i in select @extschema@.dump_statistic(relid::regclass) loop
				return next i;
			end loop;
		end loop;

		return;
	END;
$$ LANGUAGE plpgsql;



DROP FUNCTION @extschema@.dump_statistic(oid);
DROP FUNCTION @extschema@.to_attname(text,smallint);
DROP FUNCTION @extschema@.to_attnum(text,text);
DROP FUNCTION @extschema@.to_atttype(text,smallint);
DROP FUNCTION @extschema@.to_atttype(text,text);

