Postgres 9.6

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 test (
   value1 text,
   value2 text,
   value3 text,
   value4 text
);

INSERT INTO test VALUES 
('a',NULL,NULL,'d');

SELECT * FROM test;
1 rows affected



value1
value2
value3
value4


a
null
null
d


CREATE OR REPLACE FUNCTION "GetValues"(
  "@value1"                   VARCHAR(50),
  "@value2"                   VARCHAR(50),
  "@value3"                   VARCHAR(50),
  "@value4"                   VARCHAR(50)
) RETURNS SETOF test AS $$
BEGIN
  RETURN QUERY SELECT
      t."value1",
      t."value2",
      t."value3",
      t."value4"
  
  FROM test as t
  WHERE t."value1" = COALESCE("@value1", c."value1")
  AND t."value2" = COALESCE("@value2", c."value2")
  AND t."value3" = COALESCE("@value3", c."value3")
  AND t."value4" = COALESCE("@value4", c."value4");
END;
$$ LANGUAGE plpgsql;
SELECT * FROM "GetValues"('a', NULL, NULL, 'd')
ERROR:  missing FROM-clause entry for table "c"
LINE 8:   WHERE t."value1" = COALESCE("@value1", c."value1")
                                                 ^
QUERY:  SELECT
      t."value1",
      t."value2",
      t."value3",
      t."value4"
  
  FROM test as t
  WHERE t."value1" = COALESCE("@value1", c."value1")
  AND t."value2" = COALESCE("@value2", c."value2")
  AND t."value3" = COALESCE("@value3", c."value3")
  AND t."value4" = COALESCE("@value4", c."value4")
CONTEXT:  PL/pgSQL function "GetValues"(character varying,character varying,character varying,character varying) line 3 at RETURN QUERY