User Defined Functions(UDF) in PostgreSQL
As in most software languages, user defined functions are defined as a set of tasks reused/repeated throughout the code that can be replaced with a keyword function. The tasks are decided/defined by the user.
Even as a database query language, SQL has its way to provide users to do this and not only in SQL but also in other languages such as C and Python.
We look at PostgreSQL here specifically and there are mainly four types of User Defined functions (UDF).
· UDF written in SQL
· UDF written in Procedure languages
· Internal Functions
· C Language functions
SQL functions will be the essential UDF in PostgreSQL. Let us take a brief look at the other types before we delve into the SQL UDFs.
UDF procedure language are functions written in procedure languages. PostgreSQL allows UDF to be written in other languages besides C and SQL, for example Perl and Python. These are not built in on the server and need to be loaded as separate modules.
Internal Functions allow functions written in C language to be called in SQL with either the same name or different name. The syntax of internal functions specifically defines the language as ‘internal’ and must be run in strict mode. This means, any invalid or missing values throw an error, instead of a warning.
C Language functions, unlike internal functions, are compiled into shared libraries and are written in C. To be used, these functions written in C need to be added to the PostgreSQL database using the CREATE FUNCTION statement.
All user defined functions in SQL need to have CREATE FUNCTION statement to define the function though the syntax differs on the context.
Functions typically have arguments, have a body and return a value.
Arguments are the parameters fed into a function while the return statement returns the control of the execution to the main program and returns a value or even a table when needed.
The language of the body should be specified and can be placed alternatively before or after the body of the SQL function. The body of the function can be encased between dollar quotes ($$) or single quotes (‘). However, the latter may need the user to add escape characters to be able to use single quotes in the body complicating the statement so dollar quoting is the most convenient to use.
SQL UDFs by rule should have:-
· The body of the SQL function must be SQL statements with SQL row enders ;
· Not have transaction control commands or utility commands
· The overall function should either be a SELECT command or return. In cases of no value to return, a void may be used.
There can be multiple types of SQL UDFs that can be used, and we can look at a couple of examples.
The simplest UDF used is when the function returns a base type i.e. a datatype is returned.
Consider a table of order details where we can see the total price is missing and needs to be calculated.
Here we can see the order value is not available and we need a calculated value of each order detail. This can be encased in a simple function where we need not associate the total price to any separate variable.
CREATE FUNCTION totalprice(unitprice numeric,orderqty smallint) returns numeric AS $$ select unitprice * orderqty; $$ Language SQL;
Once this function is run, we can check the output sample below.
select salesorderid,unitprice,orderqty, totalprice(unitprice,orderqty) from sales.salesorderdetail limit 10;
We can also use composite type UDFs, where the argument of the function points to a row of a table or a record.
For the same scenario before, we can use a composite argument as below.
CREATE OR REPLACE FUNCTION totalprice(sales.salesorderdetail) returns numeric AS $$ select $1.unitprice * $1.orderqty; $$ Language SQL;
To check out the function – the same function now uses a composite argument.
select salesorderid,unitprice,orderqty, totalprice(sales.salesorderdetail.*) from sales.salesorderdetail limit 10;
Composite UDFs may also use composite returns as well.
For cases, where values need to be exchanged between multiple functions and procedures, SQL UDF may be created with parameters. These may be input parameters or output parameters.
Input parameters allow values to be passed inside a function while output parameters allow values to be passed out. They can be so marked IN and OUT respectively. There are also INOUT parameters that are both and VARIADIC which means undefined number of arguments presented in an array.
One commonly used UDF would be where a table is the output for SQL functions with Table return.
Consider a table of movies with maturity ratings.
If we want to filter out all movies with only specific ratings, we can create a table using a function.
Here we set the return with table and the specific columns we want to return. We also need to set that those specific columns are selected inside of the body of the function.
CREATE OR REPLACE FUNCTION filtermovies(mpaa_rating) returns TABLE(title varchar,release_year int ) AS $$ SELECT title,release_year FROM film WHERE rating = $1; $$ LANGUAGE SQL;
The function run for a specific rating can be extracted using the function with that rating. For our example, let us select the general rating movies.
select * from filtermovies('G');
There are multiple other types of User defined functions that can be created and used based on what is needed. This is a very versatile functionality that can be employed in numerous ways to further streamline SQL code.