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';
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