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?.

-- dummy helper function
CREATE OR REPLACE FUNCTION pg_temp.some_other_function(_param1 integer[], _param2 text[])
  RETURNS TABLE (x int[], y text[])
  LANGUAGE plpgsql AS
$func$
BEGIN
    RETURN QUERY
    SELECT $1, $2
    UNION ALL
    SELECT $1, $2;
END
$func$;

CREATE FUNCTION


-- dummy call
SELECT * FROM pg_temp.some_other_function('{1,2}', '{foo,bar}');


x
y


{1,2}
{foo,bar}

{1,2}
{foo,bar}



SELECT 2


-- main function
CREATE OR REPLACE FUNCTION pg_temp.return_result_table(_param1 integer[], _param2 text[])
  RETURNS text
  LANGUAGE plpgsql AS
$func$
DECLARE
   _table_name text := to_char(current_date, '"_XYZABC_"YYYY_MM_DD"_ZXCVBN"');  -- equivalent, but still not the real code
-- _select     text := '(select * from some_other_function($1, $2))';  -- broken concept
BEGIN
    EXECUTE format(
      'CREATE TABLE %I AS
       SELECT * FROM pg_temp.some_other_function($1, $2)'
    , _table_name)
    USING _param1, _param2;

    RETURN _table_name;
END
$func$;

CREATE FUNCTION


-- call:
SELECT pg_temp.return_result_table('{1,2}', '{foo,bar}')


return_result_table


_XYZABC_2023_10_18_ZXCVBN



SELECT 1


-- inspect table -- you'll have to adapt the name at later dates !!! 
TABLE "_XYZABC_2020_10_07_ZXCVBN";

ERROR:  relation "_XYZABC_2020_10_07_ZXCVBN" does not exist
LINE 2: TABLE "_XYZABC_2020_10_07_ZXCVBN";
              ^