Parsing of SQL Functions body

[LucaCappelletti94]

It is my understanding that the current implementation of CREATE FUNCTION does not support the parsing of function blocks.

For instance, the following function body gets converted to a simple DollarQuotedString, as shown below. I need to be able to parse the function bodies to write any script that may be able to execute them, so I will be getting started doing that - is there any pull request that has already attempted this that I should be aware of?

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
        -- Check that empname and salary are given
        IF NEW.empname IS NULL THEN
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary IS NULL THEN
            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
        END IF;

        -- Who works for us when they must pay for it?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- Remember who changed the payroll when
        NEW.last_date := current_timestamp;
        NEW.last_user := current_user;
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

Statement::CreateFunction {
            or_replace: false,
            temporary: false,
            if_not_exists: false,
            name: ObjectName(vec![Ident::new("emp_stamp")]),
            args: None,
            return_type: Some(DataType::Trigger),
            function_body: Some(
                CreateFunctionBody::AsBeforeOptions(
                    Expr::Value(
                        Value::DollarQuotedString(
                            DollarQuotedString {
                                value: "\n        BEGIN\n            -- Check that empname and salary are given\n            IF NEW.empname IS NULL THEN\n                RAISE EXCEPTION 'empname cannot be null';\n            END IF;\n            IF NEW.salary IS NULL THEN\n                RAISE EXCEPTION '% cannot have null salary', NEW.empname;\n            END IF;\n    \n            -- Who works for us when they must pay for it?\n            IF NEW.salary < 0 THEN\n                RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;\n            END IF;\n    \n            -- Remember who changed the payroll when\n            NEW.last_date := current_timestamp;\n            NEW.last_user := current_user;\n            RETURN NEW;\n        END;\n    ".to_owned(),
                                tag: Some(
                                    "emp_stamp".to_owned(),
                                ),
                            },
                        ),
                    ),
                ),
            ),
            behavior: None,
            called_on_null: None,
            parallel: None,
            using: None,
            language: Some(Ident::new("plpgsql")),
            determinism_specifier: None,
            options: None,
            remote_connection: None
        }

[alamb]

I think you could parse the dollar quoted string again with SQLParser as a potential workaround 🤔

[LucaCappelletti94]

I am not sure I understood, what result would you expect from that? I have found some solutions out there, but they involve compiling against Postgres, which seems overkill.

[alamb]

I am probably confused. I thought the issue was that SQLParser returned the function body as a large string but you wanted it as parsed SQL AST nodes

So i was thinking you could run the parser again, something like the following psuedo code

let create_function = parser.parse(input_sql)
// run parser again to parse function body as into AST
let function_body_as_ast = parser.parser(create_function.function_body....value);

[LucaCappelletti94]

I can try that!