Troubleshooting: SQL UDF Common Errors and Solutions ​

1. ERROR: User-defined functions are not supported
   This error is returned if the ybd_allow_udf_creation configuration parameter is not set.

CREATE SCHEMA if not exists myschema;

CREATE OR REPLACE FUNCTION myschema.add_numbers( INT4, INT4 ) 
RETURNS INT4
AS
   $$ SELECT $1 + $2; $$
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT
;

2. ERROR: SQL UDF name is too complex
   This error may occur if the UDF is too complex for the planner to process. Specifically, the planner cannot inline the UDF. A scalar function call will be inlined if all of the following conditions are met:

   • the function is LANGUAGE SQL.
   • the function is not RETURNS SETOF (or RETURNS TABLE).
   • the function is not RETURNS RECORD.
   • the function has no SET clauses in its definition.
   • the function body consists of a single, simple SELECT expression.
   • the body contains no aggregate or window function calls, no subqueries, no CTEs, no FROM clause or references to any table or table-like object, none of GROUP BY, HAVING, ORDER BY, DISTINCT, LIMIT, OFFSET, UNION, INTERSECT, EXCEPT.
   • the body query must return exactly one column.
   • the type of the body expression must match the declared return type of the function.
   • the expression must not return multiple rows. For example, from calling set-returning functions such as unnest() or generate_series().
   • if the function is declared IMMUTABLE, then the expression must not invoke any non-immutable function or operator.
   • if the function is declared STABLE, then the expression must not invoke any volatile function or operator.
   • if the function is declared STRICT, then the planner must be able to prove that the body expression necessarily returns NULL if any parameter is null. At present, this condition is only satisfied if: every parameter is referenced at least once, and all functions, operators and other constructs used in the body are themselves STRICT.
   • if an actual argument to the function call is a volatile expression, then it must not be referenced in the body more than once.
   • if an actual argument is an "expensive" expression, defined as costing more than 10 operator costs or containing any subquery, then it must not be referenced in the body more than once.

3. ERROR: cross-database references are not allowed: <database>.<schema>.<name> You cannot reference a UDF in a foreign database. Doing so will result in this error.