Is defining default values for parameters in a PostgreSQL function considered good practice, or is it something that is generally frowned upon?
Related follow-up question: If/when using function default values, is it a good idea to define default values at both the table and the function definition level, or should I just do one or the other?
I’ve been the “DBA” – and I use that term very loosely – for my company for several years now (not to mention, the “Lead Software Developer/Programmer/Engineer”, “Lead Help Desk Technician”, etc.), but I know that I’ve barely scratched the surface of what it means to be a full-fledged DBA. Just this week, I learned something that I probably should’ve known about for a while, but had simply not encountered it before or had any reason to really think about it. My revelation came in the form of default parameter values in function in PostgreSQL.
We’re currently using PostgreSQL v12.1 for our production environment, and I have a significant number of tables, views, and functions with which our in-house software interacts on a regular basis. A majority of my functions were originally defined with named parameters, although they weren’t really being used as such because they were built back before PostgreSQL could “properly” handle named parameters in the body of the function. As such, I have a large number of functions that look something like this:
CREATE FUNCTION "someinsertfunction"(column1value character varying, column2value character varying,
column3value boolean, column4value date, column5value numeric,
column6value numeric, column7value date, column8value integer,
OUT newid integer) RETURNS integer
INSERT INTO public.sometable
RETURNING sometableid INTO newid;
I’m working on replacing the numeric placeholders (
$3, etc.) with their corresponding parameter names if only to make my job a little easier when it comes to making changes to these functions down the road (I’m working on redesigning a lot of the database, so the less “confusion” I have, the better). However, as I was working through a few of these functions, I ran across a design feature that I had previously overlooked – default values for parameters. Instead of the above, I can define the function with these defaults as below to assist myself when it comes to my application programming:
CREATE FUNCTION "someinsertfunction"(column1value character varying DEFAULT NULL::character varying,
column2value character varying DEFAULT NULL::character varying,
columnvalue boolean DEFAULT FALSE,
column4value date DEFAULT NULL::date,
column5value numeric DEFAULT 0::numeric,
column6value numeric DEFAULT NULL::numeric,
column7value DEFAULT NOW()::date,
column8value integer DEFAULT NULL::integer,
OUT newid integer) RETURNS integer
The help comes in the form of, if I forget to provide a parameter/value in my code for a field, the function itself will attempt to insert this default value. My application code – at least, for all of my current/new development projects – is built to assign names to each parameter as their being added, so I can pass any, all, or none of the functions’ parameters through my object construction before actually calling/executing the function itself.
Another case where this would be helpful is if I’ve changed the table structure (adding a column) and/or the function definition at the database level but simply haven’t had time to push the code changes out to the users (happens all too often). Without defining a default value for the parameters, adding or removing a parameter to/from a function that’s already being called by my application will cause the application to fail with an error that the function does not exist.
Of course, if the table definition prohibits a particular value that’s defined as a default in the function – for example,
column_a is defined in the table as
NOT NULL, but the default value defined in the function is
NULL – the function will still fail (as it should), but at least I can give myself a little break when it comes to distributing certain programming changes that call on those functions.
I’ve considered some possible “dangers” in adding these default values, but the benefits seem to vastly outweigh the detriments, IMO. Is there something else I should be taking into account before I go
ALTER FUNCTION crazy?