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 demo (
  demo_id integer PRIMARY KEY
, demo text
, flag boolean
, log_up timestamp
);

INSERT INTO demo VALUES
  (1, 'foo', TRUE, '2013-07-24')
, (2, 'bar', FALSE, NULL)
;

CREATE TABLE

INSERT 0 2

-- return SELECT *
CREATE OR REPLACE FUNCTION f_row1()
  RETURNS SETOF demo
  LANGUAGE sql AS
'SELECT * FROM demo';

-- the same with standard-SQL syntax (pg 14+)
CREATE OR REPLACE FUNCTION f_row1_std()
  RETURNS SETOF demo
  LANGUAGE sql PARALLEL SAFE
BEGIN ATOMIC
SELECT * FROM demo;
END;

-- return SELECT <rowtype>
CREATE OR REPLACE FUNCTION f_row2()
  RETURNS TABLE (rec demo)
  LANGUAGE sql AS
'SELECT d FROM demo d';

-- the same with standard-SQL syntax (pg 14+)
CREATE OR REPLACE FUNCTION f_row2_std()
  RETURNS TABLE (rec demo)
  LANGUAGE sql PARALLEL SAFE
BEGIN ATOMIC
SELECT d FROM demo d;
END;

-- anther variant, same result
CREATE OR REPLACE FUNCTION f_row3(OUT rec demo)
  RETURNS SETOF demo
  LANGUAGE sql AS
'SELECT * FROM demo';

-- the same with standard-SQL syntax (pg 14+)
CREATE OR REPLACE FUNCTION f_row3_std(OUT rec demo)
  RETURNS SETOF demo
  LANGUAGE sql PARALLEL SAFE
BEGIN ATOMIC
SELECT * FROM demo;
END;

-- return SELECT <rowtype> + additional columns
CREATE OR REPLACE FUNCTION f_rowplus()
  RETURNS TABLE (rec demo, add_int int, add_txt text)
  LANGUAGE sql AS
$func$
SELECT d, 5, 'baz'::text FROM demo d;
$func$;

-- the same with standard-SQL syntax (pg 14+)
CREATE OR REPLACE FUNCTION f_rowplus_std()
  RETURNS TABLE (rec demo, add_int int, add_txt text)
  LANGUAGE sql PARALLEL SAFE
BEGIN ATOMIC
SELECT d, 5, 'baz'::text FROM demo d;
END;

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

SELECT * FROM f_row1();
SELECT * FROM f_row1_std();

demo_id
demo
flag
log_up

1
foo
t
2013-07-24 00:00:00

2
bar
f
null

SELECT 2

demo_id
demo
flag
log_up

1
foo
t
2013-07-24 00:00:00

2
bar
f
null

SELECT 2

SELECT * FROM f_row2();
SELECT * FROM f_row2_std();

demo_id
demo
flag
log_up

1
foo
t
2013-07-24 00:00:00

2
bar
f
null

SELECT 2

demo_id
demo
flag
log_up

1
foo
t
2013-07-24 00:00:00

2
bar
f
null

SELECT 2

SELECT * FROM f_row3();
SELECT * FROM f_row3_std();

demo_id
demo
flag
log_up

1
foo
t
2013-07-24 00:00:00

2
bar
f
null

SELECT 2

demo_id
demo
flag
log_up

1
foo
t
2013-07-24 00:00:00

2
bar
f
null

SELECT 2

-- doesn't work!
SELECT rec FROM f_row2();
SELECT rec FROM f_row2_std();

ERROR:  column "rec" does not exist
LINE 2: SELECT rec FROM f_row2();
               ^

SELECT * FROM f_rowplus();

rec
add_int
add_txt

(1,foo,t,"2013-07-24 00:00:00")
5
baz

(2,bar,f,)
5
baz

SELECT 2

SELECT * FROM f_rowplus_std();

rec
add_int
add_txt

(1,foo,t,"2013-07-24 00:00:00")
5
baz

(2,bar,f,)
5
baz

SELECT 2

SELECT rec FROM f_rowplus();
SELECT rec FROM f_rowplus_std();

rec

(1,foo,t,"2013-07-24 00:00:00")

(2,bar,f,)

SELECT 2

rec

(1,foo,t,"2013-07-24 00:00:00")

(2,bar,f,)

SELECT 2

SELECT (rec).*, add_int, add_txt FROM f_rowplus();
SELECT (rec).*, add_int, add_txt FROM f_rowplus_std();

demo_id
demo
flag
log_up
add_int
add_txt

1
foo
t
2013-07-24 00:00:00
5
baz

2
bar
f
null
5
baz

SELECT 2

demo_id
demo
flag
log_up
add_int
add_txt

1
foo
t
2013-07-24 00:00:00
5
baz

2
bar
f
null
5
baz

SELECT 2