Postgres 16

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 public.parameterbased_return_table (
  col1 text
, col2 text
, col3 int4
, col4 int4
);

INSERT INTO public.parameterbased_return_table VALUES
  ('A', 'B', 11, 22)
, ('dfdf', 'dfe', 14, 545)
RETURNING *;

CREATE TABLE




col1
col2
col3
col4


A
B
11
22

dfdf
dfe
14
545



INSERT 0 2


-- create desired return types
CREATE TYPE public.result1 AS (col1 text, col2 text);
CREATE TYPE public.result2 AS (col3 int, col4 int);

-- create polymorphis function
CREATE OR REPLACE FUNCTION public.get_parameterbased_return_table1(_return_type anyelement)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql AS
$func$
BEGIN
   CASE pg_typeof(_return_type)
   WHEN 'public.result1'::regtype THEN
      RETURN QUERY
      SELECT t.col1, t.col2
      FROM   parameterbased_return_table t;
   WHEN 'public.result2'::regtype THEN
      RETURN QUERY
      SELECT t.col3, t.col4  -- returning actual int values
      FROM   parameterbased_return_table t;
   ELSE
      -- Handle other conditions or return an empty result set
      -- just don't return anything (simpler)
   END CASE;
END
$func$;

CREATE TYPE



CREATE TYPE



CREATE FUNCTION


SELECT * FROM public.get_parameterbased_return_table1(null::public.result1);


col1
col2


A
B

dfdf
dfe



SELECT 2


SELECT * FROM public.get_parameterbased_return_table1(null::public.result2);


col3
col4


11
22

14
545



SELECT 2