Functions with Variable Input Parameters
When creating stored procedures (functions) in PostgreSQL, there's a need to handle updates to tables based on input parameters. To accommodate variable input parameters, one approach is to introduce a mode parameter. This mode parameter controls which specific parameters to use in the update query.
However, an alternative approach is to use default values for function parameters.
Default Values
By defining default values for function parameters, you can simplify the function and improve code readability. Consider the following example:
CREATE OR REPLACE FUNCTION update_site(_name text, _city text DEFAULT NULL, _telephone int DEFAULT NULL) RETURNS int LANGUAGE plpgsql AS $func$ BEGIN IF _city IS NULL AND _telephone IS NULL THEN RAISE WARNING 'At least one not-null input value required!'; RETURN; -- nothing to update END IF; UPDATE "Sites" SET "City" = COALESCE(_city, "City") , "Telephone" = COALESCE(_telephone, "Telephone") WHERE "SiteName" = _name; END $func$;
Advantages of Default Values:
Usage:
This updated function can be used with various call notations:
SELECT update_site('foo', 'New York'); -- no telephone
SELECT update_site(name => 'foo', _telephone => 123); -- no city
SELECT update_site('foo', _telephone => 123); -- still no city
Efficiency
Both approaches, using mode parameters or default values, can be efficient for simple update operations. However, for more complex scenarios, default values may provide a clearer and more maintainable solution.
Choice of Approach
The best choice zależy on specific requirements. If columns are defined as NOT NULL, default values offer a simpler and faster approach. For more complex scenarios involving SELECT queries with varying outputs, consider using VARIADIC or polymorphic input types and dynamic SQL.
The above is the detailed content of How Can Default Values Improve PostgreSQL Function Design for Variable Input Parameters?. For more information, please follow other related articles on the PHP Chinese website!