Named args in SQL functions

7 views
Skip to first unread message

Brent Moran

unread,
May 2, 2023, 10:46:08 AM5/2/23
to Mathesar Developers, Mukesh Murali
For the Removing SQLAlchemy projects, we're porting a large number of our Python functions into PL/pgSQL functions running on the database. This has a number of advantages around performance, simplicity of the codebase, and conveniences related to static typing.

One major disadvantage, however, is that we need to be a bit more careful about focusing on maintaining 'upgradability' for these functions. For the purpose of this email, assume we want to be able to run the newest version of some SQL file(s), and that should result in all needed functions being the newest version afterwards. For this, we're mostly using

CREATE OR REPLACE FUNCTION ...

The reason to use this is that it can drop the new version of a function in 'silently', replacing it (for example) in any views or other places where it's used.

Last week, @Mukesh Murali noticed a problem related to the fact that we're naming parameters for these functions. The named parameters (of course) make their intent a bit more clear, but have the disadvantage that we cannot change their name using a 'CREATE OR REPLACE FUNCTION' query.

Options (with some disadvantages noted):
1. Any time we want to change parameter names, we attempt to drop the relevant functions (without CASCADE) and provide guidance for users about how to handle dependencies manually
   - difficult, painful UX)
2.  Any time we want to change parameter names, we drop the relevant functions WITH CASCADE
   - dangerous, might result in unexpected loss of dependent objects
3. We avoid parameter names in function signatures altogether, replacing them with immediate definitions in the DECLARE block of each function.
   - Adds (massive) boilerplate :(
   - Makes function signatures less informational
   - We'd have to change all current functions (and docstrings) to conform to this style.

I'm partial to (3). While it's a regression in readability and developer experience, I think the cost to _user_ experience is too high for (1) and (2).

Gut check: What do the other devs think?

Reply all
Reply to author
Forward
0 new messages