# Postgres UDF Materialization

**URL:** https://discourse.getdbt.com/t/postgres-udf-materialization/13851
**Category:** Help
**Tags:** udf, postgres, dbt-core
**Created:** [June 17, 2024, 5:25pm UTC](https://discourse.getdbt.com/t/postgres-udf-materialization/13851 "2024-06-17T17:25:30Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![acama23](https://avatars.discourse-cdn.com/v4/letter/a/b3f665/32.png) [@acama23](https://discourse.getdbt.com/u/acama23)
#### Post date: [June 17, 2024, 5:25pm UTC](https://discourse.getdbt.com/t/postgres-udf-materialization/13851/1 "2024-06-17T17:25:30Z")

</div>

## 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

```auto
{% 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

```auto
{{ 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

```