Postgres UDF Materialization

Help
, ,
1

Problem

I’m able to create a new materialization for setting my UDFs in Postgres. The code compiles and runs as it should and there are no errors. However, when I check the database for the newly created UDF, it doesn’t appear.

The context of why I’m trying to do this

Have UDFS become part of my lineage as well as be able to reference them in the SQL. Unlike macros I like the lineage and testing that comes with this.

What I’ve already tried

A bunch of stuff. Ultimately, I take the code from the target/run for the materialized UDF and run that in the database. It works just the same. I’m able to use the UDF in subsequent modes using {{ ref(‘my_udf’)}} and that works too

Samples

Generic Postgres UDF

{% materialization udf,
adapter = 'postgres' %}
{%- set target = adapter.quote(
    this.database
) ~ '.' ~ adapter.quote(
    this.schema
) ~ '.' ~ adapter.quote(
    this.identifier
) -%}
{%- set parameter_list = config.get('parameter_list') -%}
{%- set ret = config.get('returns') -%}
{%- set lang = config.get('lang') -%}
{%- set description = config.get('description') -%}
{%- set create_sql -%}
CREATE
OR REPLACE FUNCTION {{ target }}(
    {{ parameter_list }}
) returns {{ ret }} AS $$ {{ sql }};$$ LANGUAGE {{ lang }};
{%- endset -%}
{% call statement('main') -%}
    {{ create_sql }}
{%- endcall %}

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

{% endmaterialization %}

UDF Model

{{ config(
    materialized = 'udf',
    parameter_list = 'total_lead_time int',
    returns = 'float',
    lang = 'sql',
    description = 'True when the version string matches any of an array of specs, where each spec may have an upper and lower bound. Example: 1.1.1 matches >1.1.0,<1.1.2, does not match >1.1.0,<1.1.1"'
) }}

SELECT
    CASE
        WHEN total_lead_time < 50 THEN 0.8
        WHEN total_lead_time < 160 THEN 0.5
        ELSE 0.3
    END