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 tbl (
  tbl_id     serial PRIMARY KEY,
  name       text NOT NULL,
  created_at timestamp
);

INSERT INTO tbl VALUES
  (1, 'foo', '2015-10-08 01:00')
, (2, 'bar', '2015-10-08 02:00')
, (3, 'baz', '2015-10-08 03:00');


-- plpgsql function
CREATE  FUNCTION func(_tbl_id int  = NULL
                    , _name text = NULL
                    , _created_at timestamp = NULL)
  RETURNS SETOF tbl
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE concat(
     $$SELECT * FROM tbl$$
   , concat_ws('
   AND    '
      , '
   WHERE  TRUE'
      , CASE WHEN $1 IS NOT NULL THEN 'tbl_id = $1' END
      , CASE WHEN $2 IS NOT NULL THEN 'name = $2' END
      , CASE WHEN $3 IS NOT NULL THEN 'created_at = $3' END
      )
   )
   USING $1, $2, $3;
END
$func$;


-- SQL function 1
CREATE FUNCTION func1_sql(_tbl_id int = NULL
                        , _name text = NULL
                        , _created_at timestamp = NULL)
  RETURNS SETOF tbl
  LANGUAGE sql AS
$func$
SELECT * FROM tbl a
WHERE ($1 IS NULL OR a.tbl_id = $1)
AND   ($2 IS NULL OR a.name = $2)
AND   ($3 IS NULL OR a.created_at = $3)
$func$;


-- SQL function 2
CREATE FUNCTION func2_sql(_tbl_id int = NULL
                        , _name text = NULL
                        , _created_at timestamp = NULL)
  RETURNS SETOF tbl
  LANGUAGE sql AS
$func$
SELECT * FROM tbl a
WHERE ($1 IS NULL AND a.tbl_id     IS NULL OR a.tbl_id = $1) 
AND   ($2 IS NULL AND a.name       IS NULL OR a.name = $2)
AND   ($3 IS NULL AND a.created_at IS NULL OR a.created_at = $3)
$func$;

CREATE TABLE



INSERT 0 3



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION


SELECT * FROM func(1, 'foo', '2015-10-08 01:00');


tbl_id
name
created_at


1
foo
2015-10-08 01:00:00



SELECT 1


SELECT * FROM func();


tbl_id
name
created_at


1
foo
2015-10-08 01:00:00

2
bar
2015-10-08 02:00:00

3
baz
2015-10-08 03:00:00



SELECT 3


SELECT * FROM func1_sql(1, 'foo', '2015-10-08 01:00');


tbl_id
name
created_at


1
foo
2015-10-08 01:00:00



SELECT 1


SELECT * FROM func1_sql();


tbl_id
name
created_at


1
foo
2015-10-08 01:00:00

2
bar
2015-10-08 02:00:00

3
baz
2015-10-08 03:00:00



SELECT 3


SELECT * FROM func2_sql(1, 'foo', '2015-10-08 01:00');


tbl_id
name
created_at


1
foo
2015-10-08 01:00:00



SELECT 1


SELECT * FROM func2_sql();


tbl_id
name
created_at



SELECT 0

tbl_id	name	created_at
1	foo	2015-10-08 01:00:00
2	bar	2015-10-08 02:00:00
3	baz	2015-10-08 03:00:00