Postgraphile: How to expose a stored procedure with an arbitrary return type?

[tonyhschu]

I have a stored procedure that generates an array of counts (effectively bins of a histogram) given a column in a table. It returns a table with four columns (bucket, count, min, max), like this

bucket   count      min         max
1        211        1000        2625
2        368        2700        4375
3        609        4400        6075
4        366        6100        7750
5        368        7800        9475

I can call it and it returns the right things. I granted execute permission to the role I'm using with postgraphql. Yet this function doesn't show up at all when I try it out in the graphiql interface.

I am using the forum_example as a reference and I'm able to get the search_post function to show up and run properly. What is it about this function I wrote that causes it not to show up in postgraphql?

create function schema.histogram(column_name text, table_name regclass, bins integer) returns table(bucket integer, count bigint, min double precision, max double precision) as $$
begin
return query execute format('WITH
  names AS (
    SELECT column_name
    FROM information_schema.columns
    WHERE table_name=%L and column_name=%L
  ),
  extent AS (
    SELECT max(%s) as max, min(%s) as min FROM %s
  )
  SELECT
    width_bucket(%s, extent.min, extent.max, %s),
    count(1),
    cast(min(%s) as double precision),
    cast(max(%s) as double precision)
    FROM %s, extent GROUP BY 1 ORDER BY 1;',
  table_name,
  column_name,
  column_name,
  column_name,
  table_name,
  column_name,
  bins,
  column_name,
  column_name,
  table_name
);
end;
$$ LANGUAGE plpgsql stable;

Thanks for your help,
Tony

[benjie]

Hi @tonyhschu; it looks like you're creating the function in a schema called schema - for the forum_example you need to create it in forum_example, e.g.:

create function forum_example.histogram(column_name te...

[calebmer]

@tonyhschu also PostGraphQL does not currently support the table(bucket integer, count bigint, min double precision, max double precision) syntax because in this case, you have an anonymous type. To make it work you could create a named Postgres composite type and return a setof that which should work 😊

So:

create type schema.my_type (
  bucket integer,
  count bigint,
  min double precision,
  max double precision
);

…and then change the return type to:

create function schema.histogram(column_name text, table_name regclass, bins integer) returns setof schema.my_type

This should work 😊

[tonyhschu]

Great! This worked. Thank you so much. The modified schema looks like this:

create type schema.histogram_bucket as (
  bucket integer,
  count bigint,
  min double precision,
  max double precision
);

create function schema.histogram(column_name text, table_name text, bins integer) returns setof histogram_bucket as $$
begin
return query execute format('WITH
  names AS (
    SELECT column_name
    FROM information_schema.columns
    WHERE table_name=%L and column_name=%L
  ),
  extent AS (
    SELECT max(%s) as max, min(%s) as min FROM %s
  )
  SELECT
    width_bucket(%s, extent.min, extent.max, %s),
    count(1),
    cast(min(%s) as double precision),
    cast(max(%s) as double precision)
    FROM %s, extent GROUP BY 1 ORDER BY 1;',
  table_name,
  column_name,
  column_name,
  column_name,
  table_name,
  column_name,
  bins,
  column_name,
  column_name,
  table_name
);
end;
$$ LANGUAGE plpgsql stable;

When queried like this:

{
  histogram(tableName: "people", columnName: "miles", bins: 5) {
    nodes {
      bucket,
      count,
      min
    }
  }
}

Returns something like:

{
  "data": {
    "histogram": {
      "nodes": [
        {
          "bucket": 1,
          "count": 1554,
          "min": 1000
        },
        {
          "bucket": 2,
          "count": 1668,
          "min": 7800
        },
        {
          "bucket": 3,
          "count": 1021,
          "min": 14600
        },
        {
          "bucket": 4,
          "count": 418,
          "min": 21400
        },
        {
          "bucket": 5,
          "count": 150,
          "min": 28200
        },
        {
          "bucket": 6,
          "count": 188,
          "min": 35000
        }
      ]
    }
  }
}