Product Documentation

Appearance

All platforms

SQL UDF Examples ​

Examples for SQL UDFs using various input parameters are given below:

Use only input parameters (argmode IN) ​

The example given below, adds numbers and returns an integer.

sql
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
;

SELECT myschema.add_numbers( 2, 5 );
 add_numbers
-------------
 7

Use output parameter (argmode OUT) ​

The example given below, adds numbers and returns an integer.

sql
CREATE SCHEMA if not exists myschema;
 
CREATE OR REPLACE FUNCTION add_numbers( IN INT4, IN INT4, OUT INT4 ) 
AS
   'SELECT $1 + $2;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT
;

SELECT myschema.add_numbers( 2, 5 ) as sum;
 sum
-----
   7
(1 row)

Use VARIADIC argmode ​

The example given below, retrieves the upper bound of its dimensions or the highest index of the array in integer data type.

sql
CREATE SCHEMA if not exists myschema;   

create function dfunc(a variadic int[]) 
returns int as
$$ select array_upper($1, 1) $$ language sql;

-- select dfunc() fails because no default value
select dfunc();

ERROR:  function dfunc() does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

The correct way to use variadic argmode is given below:

create or replace function dfunc(a variadic int[] default array[]::int[]) 
returns int as
$$ select array_upper($1, 1) $$ language sql;

-- Now it does not give error
select dfunc(); 

 dfunc
-------
<<[NULL]>>
(1 row)

select dfunc(10);
 dfunc
-------
     1
(1 row)

select dfunc(10,20);
 dfunc
-------
     2
(1 row)

Use INOUT argmode ​

The example given below, increments input value by the increment value.

sql
CREATE SCHEMA if not exists myschema;   

CREATE OR REPLACE FUNCTION increment_value(input_val INOUT INTEGER, increment_val INTEGER)
RETURNS INTEGER AS $$
    select input_val + increment_val;
$$ LANGUAGE sql;

select increment_value(2, 3);
 increment_value
-----------------
               5
(1 row)

Use parameters IMMUTABLE, STABLE and VOLATILE ​

The example given below, checks functions with different volatility. And checks they have the right value set in the system catalog.

sql
CREATE SCHEMA if not exists myschema;

-- CREATE FUNCTIONS WITH DIFFERENT VOLATILITY
CREATE FUNCTION functest_B_1(int) RETURNS bool LANGUAGE 'sql'
       AS 'SELECT $1 > 0';

CREATE FUNCTION functest_B_2(int) RETURNS bool LANGUAGE 'sql'
       IMMUTABLE AS 'SELECT $1 > 0';

CREATE FUNCTION functest_B_3(int) RETURNS bool LANGUAGE 'sql'
       STABLE AS 'SELECT $1 = 0';

CREATE FUNCTION functest_B_4(int) RETURNS bool LANGUAGE 'sql'
       VOLATILE AS 'SELECT $1 < 0';

SELECT proname, provolatile FROM pg_proc
       WHERE oid in ('functest_B_1'::regproc,
                     'functest_B_2'::regproc,
                     'functest_B_3'::regproc,
		             'functest_B_4'::regproc) ORDER BY proname;

   proname    | provolatile
--------------+-------------
 functest_b_1 | v
 functest_b_2 | i
 functest_b_3 | s
 functest_b_4 | v
(4 rows)

Use parameter RETURNS NULL ON NULL INPUT / STRICT ​

Use parameter RETURNS NULL ON NULL INPUT ​

In this example, if the input parameter IN is null, function example_function() returns null.

sql
CREATE SCHEMA if not exists myschema;   

CREATE OR REPLACE FUNCTION example_function(input_value INTEGER)
RETURNS INTEGER
LANGUAGE sql
RETURNS NULL ON NULL INPUT 
AS $$
SELECT CASE
         WHEN input_value IS NULL THEN 100
         ELSE input_value * 3
       END;
$$;

-- Example call with null input
select example_function(NULL);
 example_function
------------------
 <<Null>>
 (1 row)

Use parameter STRICT ​

In this example, if the input parameter IN is null, function example_function() returns null.

sql
CREATE SCHEMA if not exists myschema;   

CREATE OR REPLACE FUNCTION example_function(input_value INTEGER)
RETURNS INTEGER
LANGUAGE sql
STRICT
AS $$
SELECT CASE
         WHEN input_value IS NULL THEN 100
         ELSE input_value * 3
       END;
$$;

-- Example call with null input
select example_function(NULL);
 example_function
------------------
 <<NULL>>
 (1 row)

Use parameter CALLED ON NULL INPUT ​

CALLED ON NULL INPUT means the function f() will execute normally even if any argument is null, as opposed to automatically returning null without running the function body, which is what happens when the function is STRICT.

sql
CREATE OR REPLACE FUNCTION f(input_value INTEGER)
RETURNS Boolean
LANGUAGE sql
called on null input
AS 'SELECT $1 IS NULL'; 

select f(3); -- returns false
 f
---
 f

select f(NULL); -- returns true because we ran the function body
 f
---
 t