Include user defined SQL Functions

[olmohake]

v3-engine / postgres-ndc

Is your proposal related to a problem?

I have a sql function that does complex aggregations for a given territory and date and would like to make it available in the supergraph, but the postgres connector does not support user defined functions .

Describe the solution you'd like

Implement introspection and execution for user defined functions.

Describe alternatives you've considered

Reimplement the aggregation logic in a ts or python connector.

[robertjdominguez]

cc @BenoitRanque

[tirumaraiselvan]

An easier alternative is to use Native Queries: Here is documentation on adding native queries: https://hasura.io/docs/3.0/reference/connectors/postgresql/native-operations/native-queries

Here are few examples of native query templates that you can use for your UDFs (reference for syntax: https://www.postgresql.org/docs/current/sql-syntax-calling-funcs.html) :

SELECT * FROM search_articles(search_term => {{search_term}})

OR

SELECT * FROM search_articles(search_term := {{search_term}})

OR

SELECT * FROM search_articles({{search_term}})

Once you have added a native query to your connector, you can reintrospect and add it as a model:

1. ddn connector introspect postgres_prod
2. ddn model add search_articles

[robertjdominguez]

Given that we've outlined the option to use native queries — and that we're tracking this internally with support in the future — I'm going to close this issue, @olmohake. While it's not a priority right now, that could change should demand increase.

For anyone landing on this issue seeking UDFs directly and automatically added to your metadata when introspecting a source, please add a reaction to this comment 🙏

[robertjdominguez]

@olmohake — for public tracking, I'm going to reopen this issue and add it to our roadmap 👍

[olmohake]

I have a function with the following signature:

create function age_groups(
    area CHAR(8), 
    date text, 
    cuts INTEGER[]
) RETURNS TABLE (
    age_group TEXT,
    amount INTEGER
) AS $$
...

and defined the following native query

select * from age_groups({{area}}, {{date}}, {{cuts}})

however introspection is dropping the array and returns

{
...
"arguments": {
   "cuts":{
      "name": "cuts",
      "type":{
         "scalarType": "int4"
      }
   }
}

and accordingly the model looks like this:

kind: Model
version: v1
definition:
  name: Agegroups
  objectType: Agegroups
  arguments:
    - name: cuts
      type: Int4!

should i open a separate issue regarding array inputs?

[BenoitRanque]

@olmohake it seems we only support scalar arguments at the moment. Working on changing that, to support array arguments at least.

[BenoitRanque]

@olmohake just wanted you to know, as a workaround: this issue is only a problem with the function introspection.
This means that, as a workaround, you can manually write the configuration metadata.

Do let us know if you require help with that while we work on permanently solving this issue.