SQL UDFs

This topic covers concepts and usage details that are specific to SQL UDFs (user-defined functions).

For an introduction to UDFs, and for information that applies to all types of UDFs, see Overview of UDFs.

In this Topic:

General Usage

A SQL UDF evaluates an arbitrary SQL expression and returns the results of the expression.

The function definition can be a SQL expression that returns either a scalar (i.e. single) value or, if defined as a table function, a set of rows. For example, here is a basic example of a scalar UDF:

create or replace function area_of_circle(radius float)
returns float
as
$$
  pi() * radius * radius
$$
;

The expression can be a query expression (a SELECT expression). For example:

create or replace function profit()
returns numeric(11, 2)
as
$$
  select sum((retail_price - wholesale_price) * number_sold) from purchases
$$
;

When using a query expression in a SQL UDF, you do not need to include a semicolon within the UDF body to terminate the query expression.

Note

Although the body of a UDF can contain a complete SELECT statement, it cannot contain DDL statements or any DML statement other than SELECT.

Examples

Basic Examples

This example returns a hard-coded approximation of the mathematical constant pi.

-- ----- Basic UDF: returns pi ----- --
create or replace function pi_udf()
returns float
as
$$
3.1415926::float
$$
;

select pi_udf();

Common SQL Examples

This example uses a query expression (i.e. a SELECT statement):

-- ----- Example that uses a SELECT ----- --

-- First create a table and insert rows
create table purchases (wholesale_price numeric(11, 2), retail_price numeric(11, 2), number_sold integer);
insert into purchases (wholesale_price, retail_price, number_sold) values (10.00, 20.00, 3);
insert into purchases (wholesale_price, retail_price, number_sold) values (100.00, 200.00, 5);

-- Create the UDF
create or replace function profit()
returns numeric(11, 2)
as
$$
select sum((retail_price - wholesale_price) * number_sold) from purchases
$$
;

-- Call the UDF
select profit();

This example uses a more complex query, which includes a JOIN operation:

-- ----- Example that uses a query that contains a join ----- --

-- Create the tables and insert data
create table orders (product_ID varchar, quantity integer, price numeric(11, 2), buyer_info varchar);
create table inventory (product_ID varchar, quantity integer, price numeric(11, 2), vendor_info varchar);
insert into inventory (product_ID, quantity, price, vendor_info) values ('X24 Bicycle', 4, 1000.00, 'HelloVelo');
insert into inventory (product_ID, quantity, price, vendor_info) values ('GreenStar Helmet', 8, 50.00, 'MellowVelo');
insert into inventory (product_ID, quantity, price, vendor_info) values ('SoundFX', 5, 20.00, 'Annoying FX Corporation');
insert into orders (product_ID, quantity, price, buyer_info) values ('X24 Bicycle', 1, 1500.00, 'Jennifer Juniper');
insert into orders (product_ID, quantity, price, buyer_info) values ('GreenStar Helmet', 1, 75.00, 'Donovan Liege');
insert into orders (product_ID, quantity, price, buyer_info) values ('GreenStar Helmet', 1, 75.00, 'Montgomery Python');

-- Create the UDF
create or replace function store_profit()
returns numeric(11, 2)
as
$$
select sum( (o.price - i.price) * o.quantity) from orders as o, inventory as i where o.product_id = i.product_id
$$
;

-- Call the UDF
select store_profit();

Using UDFs in Different Clauses

A scalar UDF can be used any place a scalar expression can be used. For example:

-- ----- These examples show a UDF called from different clauses ----- --

select MyFunc(column1) from table1;

select * from table1 where column2 > MyFunc(column1);