SQLize Online / PHPize Online  /  SQLtest Online

SHOW constraint_exclusion; SET constraint_exclusion = on; CREATE TABLE t ( part int, val int ) PARTITION BY RANGE (part); CREATE TABLE t_z PARTITION OF t FOR VALUES FROM (0) TO (1); CREATE TABLE t_4 PARTITION OF t FOR VALUES FROM (6) TO (7); CREATE TABLE t_6 PARTITION OF t FOR VALUES FROM (2) TO (3); CREATE TABLE t_1 PARTITION OF t FOR VALUES FROM (10) TO (12); CREATE TABLE t_d PARTITION OF t FOR VALUES FROM (3) TO (4); CREATE TABLE t_b PARTITION OF t FOR VALUES FROM (4) TO (5); SELECT * FROM pg_constraint WHERE conrelid = 't_zz'::regclass; ALTER TABLE t_z ADD CONSTRAINT c_t_z CHECK (val between 1 and 0); ALTER TABLE t_4 ADD CONSTRAINT c_t_4 CHECK (val between 6 and 7); ALTER TABLE t_6 ADD CONSTRAINT c_t_6 CHECK (val between 2 and 3); ALTER TABLE t_1 ADD CONSTRAINT c_t_1 CHECK (val between 10 and 12); ALTER TABLE t_d ADD CONSTRAINT c_t_d CHECK (val between 3 and 4); ALTER TABLE t_b ADD CONSTRAINT c_t_b CHECK (val between 4 and 5); CREATE OR REPLACE FUNCTION get_sections(t text) RETURNS TABLE(p text) LANGUAGE sql AS $$ SELECT inhrelid::regclass FROM pg_inherits WHERE inhparent = t::regclass $$; SELECT * FROM get_sections('t') ORDER BY p DESC; CREATE OR REPLACE FUNCTION create_stv(t_name text, v_name text) RETURNS void LANGUAGE plpgsql AS $$ BEGIN EXECUTE ( SELECT format( 'CREATE OR REPLACE VIEW %I AS %s;', v_name, string_agg( 'TABLE ' || inhrelid::regclass, ' UNION ALL ' -- Порядок обхода секций — можете его поменять ORDER BY inhrelid::regclass::text DESC ) ) FROM pg_inherits WHERE inhparent = t_name::regclass ); END; $$; SELECT create_stv('t', 't_all'); EXPLAIN SELECT * FROM t_all WHERE val < 3; EXPLAIN WITH i AS ( SELECT * FROM t_all) SELECT * FROM i WHERE val < 3;