SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE apart(id bigint PRIMARY KEY, value text) PARTITION BY RANGE (id); CREATE TABLE apart1 PARTITION OF apart FOR VALUES FROM (0) TO (999999999); ALTER TABLE apart1 ADD CONSTRAINT apart1_chk CHECK (id >= 0 AND id <= 999999999); CREATE TABLE apart2 PARTITION OF apart FOR VALUES FROM (1000000000) TO (1999999999); ALTER TABLE apart2 ADD CONSTRAINT apart2_chk CHECK (id >= 1000000000 AND id <= 1999999999); CREATE TABLE apart3 PARTITION OF apart FOR VALUES FROM (2000000000) TO (2999999999); ALTER TABLE apart3 ADD CONSTRAINT apart3_chk CHECK (id >= 2000000000 AND id <= 2999999999); 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('apart', 'apart_v'); INSERT INTO apart SELECT generate_series, 'value' || generate_series::text FROM generate_series(1,1000); INSERT INTO apart SELECT generate_series, 'value' || generate_series::text FROM generate_series(1000000000,1000001000); INSERT INTO apart SELECT generate_series, 'value' || generate_series::text FROM generate_series(2000000000,2000001000); show constraint_exclusion; set constraint_exclusion=partition; explain SELECT * FROM apart_v WHERE id < 500;