Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE IF NOT EXISTS conception
(
    id bigint NOT NULL,
    name character varying(100) COLLATE pg_catalog."default" NOT NULL,
    "on" boolean NOT NULL,
    "desc" character varying(2044) COLLATE pg_catalog."default",
    "default" boolean NOT NULL DEFAULT false,
    pos_recycle bigint NOT NULL DEFAULT '-1'::integer,
    pos_temp_recycle bigint NOT NULL DEFAULT '-1'::integer,
    "timestamp" timestamp without time zone NOT NULL DEFAULT LOCALTIMESTAMP,
    group_recycle bigint NOT NULL DEFAULT '-1'::integer,
    on_root_create boolean NOT NULL DEFAULT true,
    actcatalog integer NOT NULL DEFAULT 1,
    CONSTRAINT conception_pkey PRIMARY KEY (id)
)

CREATE TABLE


CREATE TYPE argument AS
(
	name character varying,
	type character varying,
	mode character varying
);

CREATE OR REPLACE FUNCTION cfg_tablecol(
	tname character varying)
    RETURNS SETOF argument 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
DECLARE 
    --УДАЛИТЬ ПОСЛЕ ОБНОВЛЕНИЯ
    atr argument;
 BEGIN
    FOR atr IN
        SELECT a.attname, t.typname FROM pg_attribute a 
        JOIN pg_type t ON a.atttypid = t.oid
        WHERE (a.attrelid::regclass::character varying LIKE '%' || tname) AND a.attnum > 0 
        ORDER BY attnum
    LOOP
        RETURN NEXT atr;
    END LOOP;
    RETURN;
 END;
$BODY$;

CREATE TYPE



CREATE FUNCTION


SELECT  cfg_tablecol('conception');


cfg_tablecol


(id,int8,)

(name,varchar,)

(on,bool,)

(desc,varchar,)

(default,bool,)

(pos_recycle,int8,)

(pos_temp_recycle,int8,)

(timestamp,timestamp,)

(group_recycle,int8,)

(on_root_create,bool,)

(actcatalog,int4,)



SELECT 11