Returning mimetypes via aggregates only works for select=*

[wolfgangwalther]

Using the query from #2826 (comment) as an example:

WITH pgrst_source AS ( SELECT "test"."lines".* FROM "test"."lines")
SELECT
    null::bigint AS total_result_set,
    pg_catalog.count(_postgrest_t) AS page_total,
    twkb_agg(_postgrest_t::"test"."lines") AS body,
    nullif(current_setting('response.headers', true), '') AS response_headers,
    nullif(current_setting('response.status', true), '') AS response_status,
    '' AS response_inserted
FROM ( SELECT * FROM pgrst_source ) _postgrest_t;

The cast to ::"test"."lines" makes it impossible to use anything else than select=*.

I see why the cast is implemented: This allows the aggregate function to be matched on the argument type for different endpoints. But it does not allow selecting only a subset of columns or computed columns.

In my case, I just tried to implement a generic CSV handler like this:

CREATE DOMAIN "text/csv" AS text;

CREATE FUNCTION jsonb_state_transfn(state jsonb, elem anyelement) RETURNS jsonb
STABLE
PARALLEL SAFE
LANGUAGE plpgsql AS $$
BEGIN
  RETURN state || to_jsonb(elem);
END$$;

CREATE FUNCTION text_csv_agg_finalfn(state jsonb) RETURNS "text/csv"
TRANSFORM FOR TYPE jsonb
STABLE
PARALLEL SAFE
LANGUAGE plpython3u AS $$
  from pandas import DataFrame
  df = DataFrame(state)
  return df.to_csv()
$$;

CREATE AGGREGATE text_csv_agg(anyelement) (
  INITCOND  = '[]',
  STYPE     = jsonb,
  SFUNC     = jsonb_state_transfn,
  FINALFUNC = text_csv_agg_finalfn
);

But this quickly fails, because I want to select different columns, or just in a different order - and then the cast fails.

[wolfgangwalther]

As a solution, we could apply the cast only when select=*. For all other select= values, we remove the cast, thus leaving us with twkb_agg(_postgrest_t) AS body,. This should make generic handlers work better.

[steve-chavez]

As a solution, we could apply the cast only when select=*. For all other select= values, we remove the cast, thus leaving us with twkb_agg(_postgrest_t) AS body,. This should make generic handlers work better.

It's a bit messy but seems it could work.

I see why the cast is implemented: This allows the aggregate function to be matched on the argument type for different endpoints. But it does not allow selecting only a subset of columns or computed columns.

I'm wondering if we should support different arguments at all. If we have:

CREATE OR REPLACE FUNCTION twkb(anyelement) RETURNS "application/vnd.twkb" AS $$
  select twkb_agg(x);
$$ LANGUAGE SQL IMMUTABLE;

Then the user can do a pg_typeof inside that function and do a special case for a particular endpoint if needed (I believe this should be rare) or the user could also check request.path.

So how about dropping specific aggregates? It'd be breaking but it would solve select. This way we can also focus on creating pg extensions that solve this generically. Like pg_csv.

[steve-chavez]

As a solution, we could apply the cast only when select=*. For all other select= values, we remove the cast, thus leaving us with twkb_agg(_postgrest_t) AS body,. This should make generic handlers work better.

So if user does /lines?select=id,name Accept: application/vnd.twkb that would fail because function twkb_agg(lines) wouldn't work (it needs the exact type). That doesn't seem right.. it would be confusing.

I believe only allowing anyelement for the aggregate would be the right choice.

[wolfgangwalther]

Then the user can do a pg_typeof inside that function and do a special case for a particular endpoint if needed

No, this will not be possible without the cast we have right now. Otherwise the type passed into the function will be just some anonymous record type - and not the type of the endpoint.

I think casting in presence of select=* is reasonable. This is just preserving type information as long as the full row is requested. As soon as you don't request the full row anymore - it's not the same type anymore. Pretty simple.