importing posgresql function make import fail

[pierresouchay]

When parsing any function with BEGIN/END, parsing will fail:

Small example:

CREATE FUNCTION public.my_func_max_42() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
        BEGIN
          IF (SELECT SUM(mycounter::int) FROM mytable) > NEW.mycounter::int > 42
          THEN Raise Exception 'Sum must be lower than 42';
          END IF;
          RETURN NEW;
        END;
        $$;

=> This make the whole process fail, likely due to ; chars within the function

[pierresouchay]

For now, my only workaround is to remove lots of stuffs from the SQL schema using the following script:

#!/usr/bin/env python3

import os
import re
import sys


"""
An ugly script to pre-process sql and to have an output suitable for sql2dbml
"""

FUNCTION_PATTERN = re.compile("^(CREATE|ALTER) FUNCTION")
VIEW_PATTERN = re.compile("^CREATE.* VIEW")
AS_PATTERN = re.compile("[\\s]*AS ([^\\s]+)")
JSON_ACCESSOR = re.compile(" ->> '([^']+)'")

searching_for = None
current_function = ""


def print_ignored_line(val: str) -> None:
    if "DEBUG" in os.environ:
        print(f"[IGNORED] {val}", file=sys.stderr)


with open(sys.argv[1], "r") as f:
    for line in f.readlines():
        if searching_for:
            current_function += line
            if line.strip().endswith(searching_for):
                searching_for = None
                print_ignored_line(current_function)
                current_function = ""
            elif AS_PATTERN.match(line):
                symbol = AS_PATTERN.match(line).group(1)
                searching_for = f"{symbol};"
        elif FUNCTION_PATTERN.match(line) or VIEW_PATTERN.match(line):
            current_function = line
            if not line.strip().endswith(";"):
                searching_for = ";"
            else:
                print_ignored_line(line)
        else:
            # sql2dbml does not like postgresql JSON Accessors
            # replace ->> with .
            print(JSON_ACCESSOR.sub(".\\1", line), end="")

[simonschmidt]

I hit the same issue, since I produce sql with pg_dump to begin with I was able to use --disable-dollar-quoting

pg_dump \
  --schema-only \
  --disable-dollar-quoting \
  "$DB_URL" |
  sql2dbml --postgres /dev/stdin

which partially makes the conversion works - as in it doesn't error but the functions aren't part of the output.

I'm not sure if they're expected to be, this is the first time I'm trying this tool out