Support for Functions

[kiwicopple]

Feature request

Is your feature request related to a problem? Please describe.

We should implement trigger support. This is something we will need to build into our Dashboard

Describe the solution you'd like

A clean wrapper around select * from pg_proc

[soedirgo]

What does the requirement look like right now? I think supporting receive and delete are low-hanging fruits, but info about parameters are trickier:

• they can have optional names
• they can have optional default values
• they can be in, out, inout, variadic, table

Also, should this contain other kinds of "functions", i.e. aggregate functions, window functions, and procedures? DataGrip for example groups all this under "routines"—should we separate them or combine them under /routines?

@i-pip would love for you to chime in as well (and thanks again for the PR!). You might also find pg_proc useful for reference.

P.S. forgot to mention that functions with different parameters can have the same name, so turns out it's not enough to identify them by name + schema.

[kiwicopple]

should this contain other kinds of "functions", i.e. aggregate functions, window functions, and procedures?

I don't think so for now. It's not important for our use-case (user defined functions).

info about parameters are trickier

Understood. The "MVP" for this is to allow a user to create a function, then update/delete it afterwards. So ideally it would need to support these parameters.

This will be required in conjunction with triggers since triggers usually call a "function returning a trigger" like this.

For additional context, Steve is working on a library which could call a faas from Postgres (like Lambda). The ideal use-case here would be to combine with a trigger.

[i-pip]

Thanks for the invitation to chime in @soedirgo . In the create function I made no assumptions about how params are structured, they're simply a string array - this means that the details of whether they are in out inout variadic or table are left up to the user.

So a user could provide only the data types e.g. ["int", "int"], names e.g ["a int", "b integer"] or even whether it's an in or out ["a int", "out result int"]

I also made the assumption that procedures should likely be their own route like "/procedures"

I like the "/routines" idea - it doesn't even have to break current impl since it can aggregate existing routes