Postgres support

[moodgorning]

I modified your code to work with postgres, do you want me to submit a PR? it's pretty basic, but a good starting point for someone with the same problem in postgres. tested and works on my DB

[moodgorning]

Oh and thanks a lot for putting this together, the behaviour of UDFs in DBT was really annoying and this just integrates it elegantly

[brabster]

Hi that sounds great thanks! I guess there can be another .sql file alongside udf.sql like udf_postgres.sql for another adapter?

[moodgorning]

yeah something like that I think. I was playing around a bit with the return types, as I need to return SETOF [some other table] and it git a bit complicated with Schema management
Here is what I have so far:

{% materialization udf, adapter="postgres" %}
{%- set target = this.database ~ '.' ~ this.schema ~ '.' ~ this.identifier -%}

{%- set parameter_list=config.get('parameter_list') -%}
{% if  config.get('returns') == "SETOF"%}
{% if config.get('return_schema') is none %}
{%- set ret='SETOF ' ~ this.database ~ '.' ~ this.schema ~ '.' ~ config.get('return_type') -%}
{% else %}
{%- set ret='SETOF ' ~ config.get('return_schema') ~ '.' ~ config.get('return_type') -%}
{% endif %}
{% else %}
{%- set ret=config.get('return_type') -%}
{% endif %}
{%- set volatility=config.get('volatility') -%}
{%- set language=config.get('language') -%}
{%- set description=config.get('description') -%}

{%- set create_sql -%}
CREATE OR REPLACE FUNCTION {{ target }}({{ parameter_list }})
RETURNS {{ ret }}
AS $$
  {{ sql }}
$$ LANGUAGE {{ language }} {{ volatility }};
{%- endset -%}

{% call statement('main') -%}
  {{ create_sql }}
{%- endcall %}

{{ adapter.commit() }}

{{ return({'relations': []}) }}

{% endmaterialization %}

[brabster]

Feel free to just share what you have. I'm not providing any support for this repo, but happy to help folks share and collaborate.

🤔 I think if there's interest in this approach to UDFs then perhaps it's best supported by the appropriate adapter. They are likely to already have testing and quality control mechanisms set up and understand the security models. I don't suppose any explicit support from dbt Labs is needed

[moodgorning]

yeah it would be nice to have top-level support for UDFs.