When working with PostgreSQL, database developers may encounter a limitation when attempting to create routines with more than 100 arguments. PostgreSQL enforces this restriction by setting FUNC_MAX_ARGS to 100 during the server build. Although this limitation is rarely an issue, complex procedures, particularly those migrated from Oracle, may have a significantly higher number of arguments.

This blog post outlines a solution to handle such situations by utilizing composite data types to bypass the argument limit, ensuring routine compilation without significant code refactoring.

Understanding the problem

In PostgreSQL, FUNC_MAX_ARGS defines the maximum number of routine arguments allowed. By default, this value is set to 100. When migrating large procedures from Oracle or developing complex routines, you may encounter cases where the number of arguments exceeds this limit.

For example, consider an Oracle procedure that has 152 IN arguments. If you attempt to compile this in PostgreSQL, the server will return an error due to the argument count exceeding the 100-argument limit.

Example of Oracle procedure:

CREATE OR REPLACE PROCEDURE p_update(
p_id NUMBER,
p_user_id VARCHAR2 DEFAULT 'DEMO USER',
p_value_1 VARCHAR2 DEFAULT NULL,
...
p_value_150 VARCHAR2 DEFAULT NULL) IS
BEGIN
UPDATE t_table SET user_id = p_user_id,
col_1 = p_value_1,
...
col_150 = p_value_150
WHERE id = p_id;
END;

The solution: Using composite data types

The key to overcoming this limitation lies in creating a composite data type that consolidates all the IN arguments of the procedure into a single structured object. This reduces the number of arguments in the procedure definition to one, while keeping the original structure of the data intact. Importantly, OUT and INOUT arguments remain separate from the composite type.

Step 1: Create a composite data type

First, we create a composite data type that includes fields for all the IN arguments of the original routine.

CREATE TYPE p_update_args AS (
p_id NUMERIC,
p_user_id CHARACTER VARYING,
p_value_1 CHARACTER VARYING,
...
p_value_150 CHARACTER VARYING
);

Step 2: Initialize default values (optional)

If the original routine includes default values for certain arguments, you can create a function to initialize these fields with the appropriate default values.

CREATE OR REPLACE FUNCTION p_update_args$init()
RETURNS p_update_args
LANGUAGE plpgsql
AS $function$
DECLARE
l_var_type p_update_args;
BEGIN
l_var_type.p_user_id := 'DEMO USER';
RETURN l_var_type;
END;
$function$;

Step 3: Modify the procedure

Next, you modify the procedure to accept the composite data type as a single argument, while keeping the procedure logic unchanged.

CREATE OR REPLACE PROCEDURE p_update(p_par_type p_update_args)
LANGUAGE plpgsql
AS $procedure$
BEGIN
UPDATE t_table SET user_id = p_par_type.p_user_id,
col_1 = p_par_type.p_value_1,
...
col_150 = p_par_type.p_value_150
WHERE id = p_par_type.p_id;
END;
$procedure$;

Step 4: Provide a JSON-based overload (optional)

For ease of use, especially when calling the procedure from applications, you can provide an overload that accepts a JSON object as an argument and converts it to the composite type.

CREATE OR REPLACE PROCEDURE p_update(p_param json)
LANGUAGE plpgsql
AS $procedure$
DECLARE
lv_par_type p_update_args;
BEGIN
lv_par_type := json_populate_record(lv_par_type , p_param);
CALL p_update(p_par_type => lv_par_type);
END;
$procedure$;

Step 5 – Modifying the procedure call code

Here are some examples of how to call the modified procedure depending on the scenario:

  • When all arguments are known:
CALL p_update(p_par_type => ROW(201411, 'DEMO USER', 'Y', ..., 'Y'));
  • When some arguments are known and others should be initialized with default values:
DECLARE
lv_par_type p_update_args := p_update_args$init();
BEGIN
lv_par_type.p_value_1 := 'Y';
lv_par_type.p_value_150 := 'Y';
CALL p_update(p_par_type => lv_par_type);
END;
  • When only a few arguments are passed in JSON format, and the remaining arguments should default to null or default values:
CALL p_update(p_param => row_to_json((SELECT x FROM (SELECT 201411 AS p_id, 'Y' AS p_value_1, 'Y' AS p_value_150) x), TRUE));

Conclusion and best practices

By using a composite data type, you can successfully compile routines in PostgreSQL that would otherwise exceed the 100-argument limit. This approach minimizes the need for large-scale refactoring and maintains the logic of the original routine.

Key takeaways:

  • Use composite data types to consolidate IN arguments into a single structured argument.
  • Initialize default values for the composite type, if applicable.
  • Consider providing JSON-based overloads for easier integration with application code.

This solution helps streamline migrations from other databases (such as Oracle) and ensures PostgreSQL routines can handle a large number of arguments without compromising on performance or functionality. When applying this approach, ensure that all default values are passed correctly to avoid unexpected results.

Feel free to implement this solution in your projects and reach out with any questions or feedback!

Questions? Contact us