Add support for Postgres Functions

[scribahti]

Enhancement

Add support to generate CREATE OR REPLACE FUNCTION sql scripts using dbml markdown.

Syntax

Function increment {
    schema public
    returns integer
    args [len_from: integer, len_from: integer]
    body `
        DECLARE
            film_count INTEGER;
        BEGIN
            SELECT COUNT(*) 
            INTO film_count
            FROM film
            WHERE length BETWEEN len_from AND len_to;
            RETURN film_count;
        END;
    `
    language plpgsql
    behavior volatile
    security invoker
}

schema defaults to public. Can be specified inline with the function name such as Function public.increment { ... }
returns accepts any valid return type (see list below).
args accepts a list of arguments with declared types. Any return type is valid, excluding void, record, and trigger.
body accepts an expression or a multi-line expression.
language defaults to plpgsql. Can be sql, c, or internal.
behavior defaults to volatile. Can be immutable or stable.
security defaults to invoker. Can be definer.

Return types:

• void
• record
• trigger
• integer
• bool
• bytea
• date
• double_precision
• float4
• float8
• int2
• int4
• int8
• json
• jsonb
• numeric
• text
• time
• timestamp
• timestamptz
• timetz
• uuid
• varchar
• vector

Gaps

Does not implement configuration parameters.

[hdnax]

Hey @scribahti, thank you again for suggesting this & the PR.

Similar to #824, I think SQL function is a bit coupled to the specific DBMS, so maybe we should consider another approach, like enhancing the DBML parser with the ability to load user-defined (or builtin) addons or plugins? In this way, the core dbml can be lighter and more focused, while each DBMS quirks can be handled more reliably and in a more configurable manner. In fact, I think this is the vision we're likely heading towards. We would really appreciate your inputs regarding this.

By the way, can you elaborate on your use cases regarding modeling SQL functions + RLS? I'm assuming you want DBML to be the single-source-of-truth for your database config.

[scribahti]

Hey @hdnax thanks! That makes a lot of sense. I don’t know the best way organize a plugin system. Are you thinking of a separate package that sits next to /core and /parse?

And yes, that’s right—I want to use dbml to generate all my ddl scripts and version control both. I also want to use a tool like atlas, sql migrate, dbmate etc to manage migrations. In addition to RLS and functions, I want to add features for views and triggers. That should be enough for me.

Do you know anyone else using dbml like this?

[hdnax]

@scribahti Yes, there have been use cases relating to using DBML for database migrations and views. I think they are pretty good use cases.

Regarding the plugin system, there hasn't been decision to whether to split into a separate package or something else. But the @dbml/parse package should have a mean to support plugins. However, none of this has ever been decided yet and needs discussion though. In the meantime, feel free to suggest us your ideas in achieving this 😀