Convert Postgres stored functions to dbt macros about sagerx HOT 1 CLOSED

Oh snap - there's a way to use a macro to create a stored function in the database on dbt-run-start.

Source: https://medium.com/google-cloud/loading-and-transforming-data-into-bigquery-using-dbt-65307ad401cd

Begin quote from site above:

Notice that I am calling a user-defined function ch04.cleanup_numeric (target.schema is the output dataset, so ch04 here). This is defined in macros/cleanup_numeric.sql:

{% macro cleanup_numeric_macro() %}
CREATE OR REPLACE FUNCTION {{target.schema}}.cleanup_numeric(x STRING) AS
(
  IF ( x != 'NULL' AND x != 'PrivacySuppressed',
       CAST(x as FLOAT64),
       NULL )
);
{% endmacro %}

All the macros have to be executed before table creation starts. We do that by adding to dbt_project.yml the following line, to ensure that the above mentioned macro runs:

on-run-start:
    - "{{cleanup_numeric_macro()}}"

from sagerx.