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 TYPE my_test AS (foo Integer);

CREATE FUNCTION foo_out() 
  RETURNS SETOF my_test
  LANGUAGE sql AS
'SELECT ''(1)''::my_test';

CREATE FUNCTION foo1()
  RETURNS SETOF my_test
  LANGUAGE plpgsql AS
$func$
DECLARE
   x my_test;
BEGIN
   FOR x IN    
      SELECT * FROM foo_out()
   LOOP     
      RETURN NEXT x;
   END LOOP;
END
$func$;

CREATE TYPE

CREATE FUNCTION

CREATE FUNCTION

SELECT * FROM foo1();
SELECT foo1()::text;

foo

1

SELECT 1

foo1

(1)

SELECT 1

SELECT foo FROM foo_out();
SELECT * FROM foo_out();
SELECT (foo_out()).*;
SELECT (foo_out()).foo;

foo

1

SELECT 1

foo

1

SELECT 1

foo

1

SELECT 1

foo

1

SELECT 1

-- But:
SELECT foo_out()::text;

foo_out

(1)

SELECT 1

-- Error:
SELECT foo_out()::int;

ERROR:  cannot cast type my_test to integer
LINE 2: SELECT foo_out()::int;
                        ^