Postgres 13

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 dico
    ("table_name" varchar(6), "column_name" varchar(5), "address" varchar(12), "home" varchar(15), "dico" TEXT)
;
    
INSERT INTO dico
    ("table_name", "column_name", "address", "home", "dico")
VALUES
    ('table1', 'one', 'table1.one', 'myschema.table1', ''),
    ('table1', 'two', 'table1.two', 'myschema.table1', ''),
    ('table1', 'three', 'table1.three', 'myschema.table1', ''),
    ('table2', 'aaa', 'table2.aaa', 'myschema.table2', ''),
    ('table2', 'bbb', 'table2.bbb', 'myschema.table2', '')
;
5 rows affected

CREATE TABLE table1
    ("one" varchar(6), "two" varchar(5), "three" varchar(12))
;
    
INSERT INTO table1
    ("one", "two", "three")
VALUES
    ('a', 'd', 'f'),
    ('b', 'd', 'h'),
    ('a', 'd', 'g'),
    ('c', 'e', 'f'),
    ('a', 'e', 'g')
;

CREATE TABLE table2
    ("aaa" varchar(6), "bbb" varchar(5))
;
    
INSERT INTO table2
    ("aaa", "bbb")
VALUES
    ('aa', 'dd'),
    ('bbb', 'd'),
    ('aaa', 'd'),
    ('ab', 'ee'),
    ('aba', 'ee')
;





5 rows affected

5 rows affected

CREATE OR REPLACE FUNCTION public.Selecttext(tablen text, coln text)
RETURNS SETOF RECORD
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN 
    RETURN QUERY EXECUTE 'SELECT textcol::TEXT FROM (SELECT json_build_object(colname, array_agg(DISTINCT val)) AS textcol FROM '
    ||$1||E'  CROSS JOIN LATERAL json_each_text(row_to_json( '
    ||$1||E' )) AS j(colname,val) WHERE colname =  \''
    ||$2||E'\' GROUP BY colname) t2';
END 
$BODY$;
CREATE OR REPLACE FUNCTION dico_columns() RETURNS void AS $$
DECLARE

  cur_table CURSOR FOR 
    SELECT "table_name", "column_name", "address", "home", "dico"
    FROM dico
    ;
   _tabl text;
   _coln text;
   _address text;
   _home text;
   _dico text;
 

BEGIN

    OPEN cur_table;
    LOOP
    FETCH cur_table INTO _tabl,_coln,_address,_home,_dico;
     
        EXIT WHEN NOT FOUND;

	IF _home IS NOT NULL  THEN
               UPDATE dico 
                SET dico= (sELECT textcol FROM  Selecttext(_tabl,_coln) AS t(textcol TEXT)) 
                WHERE "table_name" = _tabl AND "column_name"  = _coln
                AND "home"  = _home;
				     
	END IF;

    END LOOP;
    CLOSE cur_table;
    
END ;

$$ LANGUAGE plpgsql;
    
;
SELECT dico_columns()


dico_columns





SELECT json_build_object(colname, array_agg(DISTINCT val)) AS textcol
FROM table1
CROSS JOIN LATERAL json_each_text(row_to_json(table1)) AS j(colname,val) 
WHERE colname='one' 
GROUP BY colname


textcol


{"one" : ["a","b","c"]}


sELECT textcol FROM  Selecttext('table1','one') AS t(textcol TEXT)


textcol


{"one" : ["a","b","c"]}


sELECT textcol FROM  Selecttext('table1','two') AS t(textcol TEXT)


textcol


{"two" : ["d","e"]}


SELECT * frOM dico


table_name
column_name
address
home
dico


table1
one
table1.one
myschema.table1
{"one" : ["a","b","c"]}

table1
two
table1.two
myschema.table1
{"two" : ["d","e"]}

table1
three
table1.three
myschema.table1
{"three" : ["f","g","h"]}

table2
aaa
table2.aaa
myschema.table2
{"aaa" : ["aa","aaa","ab","aba","bbb"]}

table2
bbb
table2.bbb
myschema.table2
{"bbb" : ["d","dd","ee"]}

table_name	column_name	address	home	dico
table1	one	table1.one	myschema.table1	{"one" : ["a","b","c"]}
table1	two	table1.two	myschema.table1	{"two" : ["d","e"]}
table1	three	table1.three	myschema.table1	{"three" : ["f","g","h"]}
table2	aaa	table2.aaa	myschema.table2	{"aaa" : ["aa","aaa","ab","aba","bbb"]}
table2	bbb	table2.bbb	myschema.table2	{"bbb" : ["d","dd","ee"]}