Is it possible to nest an EXECUTE statement as a source recordset in PostgreSQL?

Question

This is the case of a dynamic _sql select passed to a function that tries to execute the query and return a JSON array with the result.

create or replace function jlist_objects_bysql (
  _sql  varchar
)
returns json
as $$
  select
    json_agg (t)
  from (
    execute _sql
  ) as t;
$$ language sql;

The validation error in PostgreSQL 12 is

psql:objects_procedures.sql:874: ERROR:  syntax error at or near "t"
LINE 8:   from execute _sql t;

Answer 1

Dynamic SQL does not work with the language sql but rather with language plpgsql.

Then, you can enlarge the dynamic query

create or replace function jlist_objects_bysql (
  _sql  varchar
)
returns json
as $$
declare
    output json;
BEGIN
 execute 'select json_agg (t) from (   ' || _sql || '
  ) as t;'
  INTO output;
  
  return output;
END
$$ language plpgsql;


select jlist_objects_bysql('select * from test');