Handle more function source cases

[nyurik]

There are some function source cases that I think Martin should handle. They already exist, e.g. generated by OpenMapTiles is getmvt(zoom,x,y). Proposed changes:

• Support cases when the parameter is zoom rather than z.
• The function may only have 3 parameters, without the extra query_params json value
• The function may return a table with a single row and two columns rather than a single bytea value. OpenMapTiles's generate-sqltomvt would create a table where first column is the tile data (bytea), and the second is a string with hash (md5) value of the tile.
• The function may return GZIPed tile. It should be possible to detect such cases, but this will require an initial test.

The current function source search implementation looks for any function whose parameters contain (!) z, x, y, query_params (3 ints and a json). I suspect this is a bug because there could be other params before z, and this is not checked in code. Plus this excludes valid cases without query_params and the table.

---

Implementation ideas

I think we should change the above query to something like this, and possibly do some additional validation by requesting a tile during startup like I did in tilelive-pgquery

-- Find SQL functions that match these criteria:
--     * The function must have 3 or 4 input parameters,
--       first 3 must be integers and named z,x,y (in that order),
--       with the optional JSON "query_params" as the 4th parameter.
--       The first param could be "zoom" instead of "z".
--     * The function output must be either a single bytea value or a table,
--       with the table row being either [bytea] or [bytea, text] (in that order).
-- Output fields:
--   schema: the schema the function is in
--   name: the function name
--   inputs: a JSON array of input parameters [{name: type}, ...]
--   outputs: a JSON value - either a string or an array:
--            for a single-value functions, this is the function type as a string, e.g. "bytea"
--            if the function returns a table, this is an array of column types, e.g. ["bytea", "text"]
WITH
inputs AS (
    -- list of input parameters for each function, returned as a jsonb array [{name: type}, ...]
    SELECT
        specific_name,
        jsonb_agg(jsonb_build_object(COALESCE(parameter_name::text, '_'), data_type::text) ORDER BY ordinal_position) as input_params
    FROM information_schema.parameters
    WHERE parameter_mode = 'IN'
    GROUP BY specific_name
),
outputs AS (
    -- list of output parameters for each function, returned as a jsonb array [{name: type}, ...]
    SELECT
        specific_name,
        jsonb_agg(data_type::text ORDER BY ordinal_position) as output_params
    FROM information_schema.parameters
    WHERE parameter_mode = 'OUT'
    GROUP BY specific_name
)
SELECT
    routines.specific_schema AS schema,
    routines.routine_name AS name,
    inputs.input_params AS inputs,
    COALESCE(outputs.output_params, to_jsonb(routines.data_type::text)) AS output_type
FROM
    information_schema.routines JOIN inputs ON routines.specific_name=inputs.specific_name
        LEFT JOIN outputs ON routines.specific_name=outputs.specific_name
WHERE
 input_params IN (
        '[{"z": "integer"}, {"x": "integer"}, {"y": "integer"}]'::jsonb,
        '[{"zoom": "integer"}, {"x": "integer"}, {"y": "integer"}]'::jsonb,
        '[{"z": "integer"}, {"x": "integer"}, {"y": "integer"}, {"query_params": "json"}]'::jsonb,
        '[{"zoom": "integer"}, {"x": "integer"}, {"y": "integer"}, {"query_params": "json"}]'::jsonb)
    AND (
        (routines.data_type = 'bytea' AND output_params IS NULL)
        OR (routines.data_type = 'record' AND output_params IN (
        '["bytea"]'::jsonb,
        '["bytea", "text"]'::jsonb
        )));