Postgres 14

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(id int, col1 int);
INSERT INTO tbl VALUES (1,11), (2,22), (3,33), (4,44), (5,55);

CREATE TABLE

INSERT 0 5

-- 1)
CREATE OR REPLACE FUNCTION get_1(_param_id integer)
  RETURNS integer
  LANGUAGE plpgsql AS
$func$
BEGIN
   -- RETURN _param_id;
   RETURN (SELECT col1 FROM tbl WHERE id = _param_id);
END
$func$;

SELECT get_1(1);

CREATE FUNCTION

get_1

11

SELECT 1

-- 2)
CREATE OR REPLACE FUNCTION get_2(_param_id integer, OUT _col1 integer)
-- RETURNS integer  -- optional noise in this case
  LANGUAGE plpgsql AS  
$func$
BEGIN
   SELECT INTO _col1  col1 FROM tbl WHERE id = _param_id;

   -- also valid, but discouraged:
   -- _col1 := col1 FROM TABLE WHERE id = _param_id;
END
$func$;

SELECT get_2(2);

CREATE FUNCTION

get_2

22

SELECT 1

-- 3)
CREATE OR REPLACE FUNCTION get_3(_param_id integer)
  RETURNS integer
  LANGUAGE plpgsql AS
$func$
BEGIN
   SELECT INTO _param_id  col1 FROM tbl WHERE id = _param_id;
   RETURN _param_id;

   -- Also vlaid, but discouraged:
   -- $1 := col1 FROM tbl WHERE id = $1;
   -- RETURN $1;
END
$func$;

SELECT get_3(3);

CREATE FUNCTION

get_3

33

SELECT 1

-- 4)
CREATE OR REPLACE FUNCTION get_4(_param_id integer, INOUT _col1 integer = 123)
  RETURNS integer
  LANGUAGE plpgsql AS
$func$
BEGIN
   -- You can assign some (other) value to _col1:
   -- SELECT INTO _col1  col1 FROM tbl WHERE id = _param_id;
   -- If you don't, the DEFAULT 123 will be returned.
END
$func$;

SELECT get_4(4) AS default, get_4(4, 666) AS override;

CREATE FUNCTION

default
override

123
666

SELECT 1

-- 5)
CREATE OR REPLACE FUNCTION get_5(_param_id integer)
  RETURNS integer
  LANGUAGE sql AS
'SELECT col1 FROM tbl WHERE id = _param_id';

SELECT get_5(5);

CREATE FUNCTION

get_5

55

SELECT 1