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

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

CREATE FUNCTION @extschema@.to_schema_qualified_collation(colloid oid) RETURNS TEXT AS $$
	DECLARE
		result text;

	BEGIN
		if colloid = 0 then
			return '0::oid';
		end if;

		select quote_literal(collname)
		from pg_catalog.pg_collation inner join pg_catalog.pg_namespace
				on collnamespace = pg_namespace.oid
		where pg_collation.oid = colloid
		into result;

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

		-- TODO: Currently there is no ::regcollation type cast similarly to
		--       'pg_catalog.pg_class'::regclass::oid. Consider changing this
		--       function once it will be added.
		return format('(SELECT oid FROM pg_catalog.pg_collation WHERE collname = %s)', result);
	END;
$$ LANGUAGE plpgsql STRICT;

CREATE OR REPLACE 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::oid) != @extschema@.to_namespace('information_schema')
				and @extschema@.get_namespace(starelid::oid) != @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?
			];

			-- If PG version >= 12, then dump stacoll1-5 columns as well.
			IF (SELECT REGEXP_REPLACE((SELECT setting FROM pg_settings WHERE name = 'server_version'), '[^\d]+.*', '')::integer) >= 12 THEN

				stacols := array['stakind',
								 'staop',
								 'stacoll',
								 'stanumbers',
								 'stavalues'];
				ncols := 31;
				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))),

					-- stacoll
					@extschema@.to_schema_qualified_collation(r.stacoll1::oid),
					@extschema@.to_schema_qualified_collation(r.stacoll2::oid),
					@extschema@.to_schema_qualified_collation(r.stacoll3::oid),
					@extschema@.to_schema_qualified_collation(r.stacoll4::oid),
					@extschema@.to_schema_qualified_collation(r.stacoll5::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'
				];

			-- If PG version < 12, then perform as usual.
			ELSE

				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'
				];

			END IF;

			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;
