1

I'm using Postgres 9.2 to generate some JSON data. For each nested table I'm doing this nested set of functions: 

SELECT array_to_json(
  coalesce(
    array_agg(
      row_to_json(foo)),
    ARRAY[]::json[])
)
FROM foo

The effect is to create a json array with each row being the json collection for the row. The coalesce ensures that I get an empty array rather than nil if the table is empty. In most cases foo is actually a subquery but I don't think that is relevent to the question.

I want to create a function table_to_json_array(expression) such that this has the same effect as above:

SELECT table_to_json_array(foo) FROM foo

I need to use this lots so I was planning to create a Postgres function to have the effect of the combination of these calls to clean up my queries. Looking at the documentation it seems as if I need to create an aggregate rather than a function to take a table argument but those look like I would need to reimplement array_agg myself.

Have I missed something (possibly just the type a function would need to take)? Any suggestions?

klin
  • 112,967
  • 15
  • 204
  • 232
Joseph Lord
  • 6,446
  • 1
  • 28
  • 32

2 Answers2

2

In most cases foo is actually a subquery but I don't think that is relevent to the question.

Unfortunately, it is. You can create a function with regclass argument:

create or replace function table_to_json(source regclass)
returns json language plpgsql
as $$
declare
    t json;
begin
    execute format ('
        SELECT
            array_to_json(
                coalesce(array_agg(row_to_json(%s)),
                ARRAY[]::json[]))
        FROM %s', source, source)
        into t;
    return t;
end $$;

select table_to_json('my_table');
select table_to_json('my_schema.my_view');

But in context:

select table_to_json_rec(arg)
from (select * from my_table) arg

the argument arg is of type record. PL/pgSQL functions cannot accept type record. The only way to get this is a C function, what I guess is not an option. The same goes for aggregates (you must have a function to define an aggregate).

klin
  • 112,967
  • 15
  • 204
  • 232
  • 1
    The text representation of a `regclass` value is automatically quoted where needed, so it must be `format('.. %s ..', source)`. `%I` would be wrong and fail for identifiers that have to be quoted. [Details here.](http://stackoverflow.com/questions/10705616/table-name-as-a-postgresql-function-parameter/10711349#10711349) – Erwin Brandstetter Jun 04 '14 at 22:18
  • Thanks, source was text in first version. – klin Jun 04 '14 at 22:20
  • Judging from your answers you know the details. I leave the comment for the general public, since it's a common mistake. – Erwin Brandstetter Jun 04 '14 at 22:28
1

Postgres 9.3 adds a json_agg function which simplifies the specific query I need although this isn't a general solution to the aggregate functions issue. It still needs a coalesce function to ensure the empty set is properly returned.

SELECT coalesce( json_agg(foo), json'[]')
FROM foo

And it works even when foo is a subquery.

Joseph Lord
  • 6,446
  • 1
  • 28
  • 32