unpivot.sql

-- SELECT s as id, lpad(s::text,4,'0'::text) code, s+100 A, s*2 B , s/25.C INTO pivotdata FROM generate_series(1,100) s

-- SELECT (select row_to_json(_) from (select id, code) as _)::jsonb pivotid, 'a'::text pivotkey, a::text pivotvalue FROM pivotdata;
-- DROP FUNCTION unpivot_table(regclass,text[],text[],boolean);


CREATE OR REPLACE FUNCTION unpivot_table ( in tableid regclass, in pivotids text[], in pivotkeys text[] DEFAULT NULL::text[], checkfor boolean DEFAULT true,
out pivotid jsonb , out pivotkey text, out pivotvalue text) RETURNS setof record
AS
$BODY$
DECLARE
recpiv record;
coljsonb text;
coltest text[];
colmissing text[];
BEGIN

coltest := (SELECT array_agg(attname) FROM pg_attribute WHERE attrelid=tableid AND attnum >0 AND NOT attisdropped);
IF NOT pivotkeys IS NULL AND NOT coltest @> pivotkeys THEN
	colmissing := pivotkeys;
	FOR i IN 1 .. array_length(coltest,1) LOOP
		colmissing := array_remove(colmissing,coltest[1]);
	END lOOP;
	RAISE EXCEPTION 'Asked colum(s) (%) are not present in table' , array_to_string(colmissing,'/');
END IF; 

FOR i IN 1 .. array_length(pivotids,1)  LOOP
    IF i=1 THEN coljsonb := quote_ident(pivotids[i]);
    ELSE coljsonb := coljsonb || ',' || quote_ident(pivotids[i]);
    END IF;
END LOOP;

FOR recpiv IN SELECT attname FROM pg_attribute WHERE attrelid=tableid AND attnum >0 AND NOT attisdropped
	AND ((pivotkeys IS NULL AND NOT attname = ANY(pivotids))
	OR (checkfor AND attname = ANY(pivotkeys))
	OR (NOT checkfor AND NOT attname = ANY(pivotkeys) AND NOT attname = ANY(pivotids))
	)
LOOP
    RETURN QUERY EXECUTE $$SELECT (select row_to_json(_) from (select $$||coljsonb||$$) as _)::jsonb pivotid, $$||quote_literal(recpiv.attname)||$$::text pivotkey, $$||quote_ident(recpiv.attname)||$$::text pivotvalue FROM $$||tableid::text||$$;$$;
END LOOP;
RETURN;
END
$BODY$
LANGUAGE PLPGSQL;

SELECT * FROM unpivot_table('pg_catalog.pg_tables','{tablename,schemaname}')