SQLize Online / PHPize Online  /  SQLtest Online

-- DROP FUNCTION public.f_pivot(text, text); CREATE OR REPLACE FUNCTION public.f_pivot(central_query text, headers_query text) RETURNS SETOF record LANGUAGE plpgsql VOLATILE AS $$ DECLARE left_column text; header_column text; value_column text; h_value text; headers_clause text := ''; query text; j json; r record; i int := 1; BEGIN -- find the column names of the source query EXECUTE 'SELECT row_to_json(_r.*) FROM (' || central_query || ') AS _r' INTO j; FOR r IN SELECT * FROM json_each_text(j) LOOP IF (i = 1) THEN left_column := r.key; ELSIF (i = 2) THEN header_column := r.key; ELSIF (i = 3) THEN value_column := r.key; END IF; i := i + 1; END LOOP; -- build the dynamic transposition query (based on the canonical model) FOR h_value IN EXECUTE headers_query LOOP headers_clause := concat(headers_clause, format(chr(10) || ', min(CASE WHEN %I = %L THEN %I::text END) AS %I', header_column, h_value, value_column, h_value )); END LOOP; query := format('SELECT %I %s FROM (SELECT *, row_number() OVER () AS rn FROM (%s) AS _c) AS _d GROUP BY %I ORDER BY min(rn)', left_column, headers_clause, central_query, left_column); -- Return the result set directly RETURN QUERY EXECUTE query; END; $$ EXECUTE ON ANY;