User-defined functions (UDF)

Andrey Aksenov

Greengage DB supports user-defined functions. This lets you add custom operations that are not available as built-in functions and reuse them in your SQL statements. See the Extending SQL section in the PostgreSQL documentation for more information.

Greengage DB provides the following types of functions:

SQL functions — functions written in SQL. See examples in the Examples: query language (SQL) functions section.
Procedural language (PL) functions — functions written in languages other than SQL and C (for example, PL/pgSQL or PL/Python). See examples in the Examples: procedural language functions section.
Internal functions — C functions statically linked into the database server. See the Internal Functions section in the PostgreSQL documentation for more information.
C-language functions — C functions compiled into dynamically loadable shared libraries (also called shared objects) that the server loads on demand. See the C-Language Functions section in the PostgreSQL documentation for more information.

You can use the CREATE FUNCTION command to define a new function. Its simplified syntax is shown below:

CREATE [OR REPLACE] FUNCTION <name>
    ( [ [<argmode>] [<argname>] <argtype>
       [ { DEFAULT | = } <default_expr> ] [, ...] ] )
    RETURNS { <rettype>
       | TABLE ( <column_name> <column_type> [, ...] ) }
    AS $$
    <definition>
    $$
    LANGUAGE <langname>
    [ IMMUTABLE | STABLE | VOLATILE ]
    [ EXECUTE ON { ANY | MASTER | ALL SEGMENTS | INITPLAN } ];

The following components are used to define a function:

Function name

<name> specifies the name (optionally schema-qualified) of the function. Within a schema, it must be unique for a given set of input argument types. Functions with the same name but different argument types can coexist, allowing function overloading.
Function arguments

To define function arguments, the following parameters and clauses are used:
- <argtype> — specifies the data types of the function’s arguments (optionally schema-qualified), if any. Argument types can be a base type, composite type, domain, or may reference the type of a table column.
  
  Example: add_numbers(INT, INT).
- (Optional) <argname> — allows you to give a name to an argument.
  
  Example: multiply_numbers(a INT, b INT).
- (Optional) <argmode> — specifies the mode of an argument: IN, OUT, INOUT, or VARIADIC.
  
  Example: sum_numbers(VARIADIC nums INT[]).
- (Optional) DEFAULT <default_expr> — defines a default value to be used if the argument is not provided.
  
  Example: greet(msg TEXT DEFAULT 'world').
Function return type

<rettype> specifies the return data type (optionally schema-qualified). The return type can be a base type, composite type, domain, or may reference the type of a table column.
Function definition

<definition> is a string constant defining the function; the meaning depends on the language. It may be an internal function name, the path to an object file, an SQL command, or text in a procedural language.

Example: SELECT 'Hello, world!';.
Language

<langname> is the name of the language that the function is implemented in. Can be sql, c, internal, or the name of a procedural language. See Types of functions.
Volatility

The volatility attributes (IMMUTABLE, STABLE, VOLATILE) indicate how stable a function’s result is with respect to its input arguments and database state. In general, volatility indicates when the function is run. See Function volatility.
EXECUTE ON attributes

The EXECUTE ON clause specifies where the function runs in a Greengage DB cluster. See EXECUTE ON attributes.

The user that creates the function becomes the owner of the function. To be able to create a function, the user must have the USAGE privilege on the argument types and the return type.

This example defines an SQL function greet() using CREATE FUNCTION, with a default text argument that returns a greeting message:

CREATE FUNCTION greet(msg TEXT DEFAULT 'world')
    RETURNS TEXT
AS
$$
SELECT 'Hello, ' || msg || '!';
$$ LANGUAGE sql
    IMMUTABLE
    EXECUTE ON MASTER;

Call greet() without arguments to use the default value:

SELECT greet();

The result looks like this:

     greet
---------------
 Hello, world!
(1 row)

You can also provide a specific argument:

SELECT greet('Alice');

The result looks as follows:

     greet
---------------
 Hello, Alice!
(1 row)

You can alter an existing function using one of the following commands:

CREATE OR REPLACE FUNCTION — replaces the function body or attributes (such as language or volatility) while keeping the same name, argument types, and return type.
ALTER FUNCTION — changes function properties (such as ownership, name, or execution location) but does not modify the function body or its argument/return types.

You cannot use these commands to change a function’s argument types or return type. To do that, you must drop and recreate the function. For functions with OUT parameters, this means their types cannot be changed without dropping the function.

Dropping and recreating a function also requires dropping and recreating any dependent objects (such as rules, views, or triggers). To avoid this, use CREATE OR REPLACE FUNCTION to update a function definition without breaking dependencies.

The following example defines a greet() function:

CREATE FUNCTION greet(msg TEXT DEFAULT 'world')
    RETURNS TEXT
AS
$$
SELECT 'Hello, ' || msg || '!';
$$ LANGUAGE sql
    IMMUTABLE
    EXECUTE ON MASTER;

The CREATE OR REPLACE FUNCTION statement updates the definition of the greet() function:

CREATE OR REPLACE FUNCTION greet(msg TEXT DEFAULT 'world')
    RETURNS TEXT
AS
$$
SELECT 'Hello, ' || msg || '! Have a great day!';
$$ LANGUAGE sql
    IMMUTABLE
    EXECUTE ON MASTER;

You can verify the updated function by calling it:

SELECT greet('Alice');

The expected result is:

              greet
---------------------------------
 Hello, Alice! Have a great day!
(1 row)

Use ALTER FUNCTION to rename a function, for example, from greet() to greet_user():

ALTER FUNCTION greet(TEXT) RENAME TO greet_user;

Functions in Greengage DB are assigned a volatility attribute: IMMUTABLE, STABLE, or VOLATILE. These PostgreSQL-based attributes describe when a function is evaluated during query processing. For example, an IMMUTABLE function can be evaluated at query planning time, while a VOLATILE function must be evaluated for every row. The table below summarizes the volatility attributes and their support in Greengage DB.

Function attribute Support in Greengage DB Description Comments

Function attribute	Support in Greengage DB	Description	Comments
IMMUTABLE	Yes	Relies only on information directly in its argument list. Given the same argument values, always returns the same result	—
STABLE	Yes, in most cases	Within a single SQL statement, returns the same result for the same argument values, but results can change across statements	Results depend on database lookups or parameter values. The `current_timestamp` family of functions is `STABLE` because their values do not change within a single transaction, and therefore also remain the same within a single statement
VOLATILE (default)	Restricted	Function values can change within a single table scan. Examples: `random()`, `timeofday()`	Any function with side effects is volatile, even if its result is predictable. Example: `setval()`

IMMUTABLE

Yes

Relies only on information directly in its argument list. Given the same argument values, always returns the same result

—

STABLE

Yes, in most cases

Within a single SQL statement, returns the same result for the same argument values, but results can change across statements

Results depend on database lookups or parameter values. The current_timestamp family of functions is STABLE because their values do not change within a single transaction, and therefore also remain the same within a single statement

VOLATILE (default)

Restricted

Function values can change within a single table scan. Examples: random(), timeofday()

Any function with side effects is volatile, even if its result is predictable. Example: setval()

In Greengage DB, data is divided across segments, each running as a separate PostgreSQL instance. To avoid inconsistent or unexpected results, functions marked as STABLE or VOLATILE should not run on segments if they contain SQL commands or modify database state. For example, functions such as random(), timeofday(), or setval() are restricted on distributed data because their results would differ across segments.

You can safely use STABLE and VOLATILE functions in queries that execute only on the master. This includes statements without a FROM clause, such as:

SELECT setval('myseq', 201);
SELECT foo();

If a query contains a FROM clause with a distributed table and the function returns a set of rows, the function may execute on the segments:

SELECT * FROM foo();

In summary, use IMMUTABLE functions freely across segments, but restrict STABLE and VOLATILE functions to queries that run on the master to ensure consistent results.

Greengage DB provides EXECUTE ON attributes to control where a function runs. These attributes are specific to Greengage DB and determine whether a function executes on the master, on all segments, or anywhere in the cluster. For example, a function defined with EXECUTE ON MASTER runs only on the master instance, while a function with EXECUTE ON ALL SEGMENTS runs on all primary segment instances (not the master). The table below summarizes the available EXECUTE ON attributes.

Function attribute	Description	Comments
EXECUTE ON ANY (default)	Indicates that the function can be run on the master or any segment instance, and it returns the same result regardless of where it runs	Greengage DB determines where the function runs
EXECUTE ON MASTER	Indicates that the function must be run on the master instance	Specify this attribute if the user-defined function runs queries to access tables
EXECUTE ON ALL SEGMENTS	Indicates that for each invocation, the function must be run on all primary segment instances, but not the master	—
EXECUTE ON INITPLAN	Indicates that the function contains an SQL command that dispatches queries to the segment instances and requires special processing on the master instance by Greengage DB when possible	—

Function attribute

Description

Comments

EXECUTE ON ANY (default)

Indicates that the function can be run on the master or any segment instance, and it returns the same result regardless of where it runs

Greengage DB determines where the function runs

EXECUTE ON MASTER

Indicates that the function must be run on the master instance

Specify this attribute if the user-defined function runs queries to access tables

EXECUTE ON ALL SEGMENTS

Indicates that for each invocation, the function must be run on all primary segment instances, but not the master

—

EXECUTE ON INITPLAN

Indicates that the function contains an SQL command that dispatches queries to the segment instances and requires special processing on the master instance by Greengage DB when possible

—

A user-defined function that runs only SELECT commands on replicated tables can execute on segments. Replicated tables store all rows on every segment, so it is safe to read them from segments. However, any SQL command that modifies data in a replicated table must run on the master instance.

In summary, functions can run read-only queries on replicated tables from segments, but all data-modifying commands must be executed on the master.

NOTE

The hidden system columns (ctid, cmin, cmax, xmin, xmax, and gp_segment_id) cannot be referenced in user queries on replicated tables because they have no single, unambiguous value.

Arguments of an SQL function can be referenced in the function body either by position ($1, $2, and so on) or by name. The example below uses positional references:

CREATE FUNCTION add_numbers(INT, INT)
    RETURNS INT
AS
$$
SELECT $1 + $2;
$$ LANGUAGE sql
    IMMUTABLE;

Call the function with two arguments:

SELECT add_numbers(3, 7);

The result is:

 add_numbers
-------------
          10
(1 row)

You can reference named arguments directly in an SQL function body:

CREATE FUNCTION multiply_numbers(a INT, b INT)
    RETURNS INT
AS
$$
SELECT a * b;
$$ LANGUAGE sql
    IMMUTABLE;

Call the function using named arguments:

SELECT multiply_numbers(a := 2, b := 3);

The result is:

 multiply_numbers
------------------
                6
(1 row)

SQL functions can use polymorphic argument types such as ANYELEMENT or ANYARRAY. These allow a function to accept different data types for its arguments, making it reusable with integers, floats, or other compatible types.

This example shows a function that subtracts two values using a polymorphic type:

CREATE FUNCTION subtract_numbers(a ANYELEMENT, b ANYELEMENT)
    RETURNS ANYELEMENT
AS
$$
SELECT a - b;
$$ LANGUAGE sql
    IMMUTABLE;

Call the function with integer arguments:

SELECT subtract_numbers(10, 4);

The result looks as follows:

 subtract_numbers
------------------
                6
(1 row)

Call the function with numeric arguments:

SELECT subtract_numbers(10.5, 4.0);

The result is:

 subtract_numbers
------------------
              6.5
(1 row)

SQL functions in Greengage DB can accept a variable number of arguments using the VARIADIC keyword.

In this example, a function sums any number of integers:

CREATE FUNCTION sum_numbers(VARIADIC nums INT[])
    RETURNS BIGINT
AS
$$
SELECT SUM(n)
FROM unnest(nums) AS n;
$$ LANGUAGE sql
    IMMUTABLE;

Call the function with four arguments:

SELECT sum_numbers(1, 2, 3, 4);

Call the function with two arguments:

SELECT sum_numbers(5, 10);

SQL functions can define output parameters using the OUT keyword. This allows a function to return multiple values as named columns without explicitly using RETURNS TABLE.

The function below calculates the sum and difference of two numbers:

CREATE FUNCTION calculate_stats(a NUMERIC, b NUMERIC, OUT sum_result NUMERIC, OUT diff_result NUMERIC)
AS
$$
SELECT a + b, a - b;
$$ LANGUAGE sql
    IMMUTABLE;

Call the function and return all output columns:

SELECT *
FROM calculate_stats(10, 4);

The result looks as follows:

 sum_result | diff_result
------------+-------------
         14 |           6
(1 row)

You can also access a single output column using the following syntax:

SELECT (calculate_stats(10, 4)).sum_result;

The result is:

 sum_result
------------
         14
(1 row)

SQL functions can return a composite type, which allows multiple related values to be returned as a single structured result.

Create a composite type to hold the results:

CREATE TYPE stats_result AS
(
    sum_result  NUMERIC,
    diff_result NUMERIC
);

Create a function that returns the composite type:

CREATE FUNCTION calculate_stats(a NUMERIC, b NUMERIC)
    RETURNS stats_result
AS
$$
SELECT a + b, a - b;
$$ LANGUAGE sql
    IMMUTABLE;

Sample 'sales' table

To follow the examples in this section, create the sales table:

CREATE TABLE sales
(
    id       INT,
    date     DATE,
    amount   DECIMAL(10, 2),
    category TEXT
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id);

Insert data:

INSERT INTO sales
VALUES (1, '2025-08-01', 100.00, 'Books'),
       (2, '2025-08-02', 200.00, 'Electronics'),
       (3, '2025-08-03', 150.00, 'Books'),
       (4, '2025-08-03', 300.00, 'Furniture');

SQL functions can accept row or table types as arguments and return computed values.

The function below calculates a discounted amount for a single sales row:

CREATE FUNCTION get_discount_amount(s sales, pct DECIMAL)
    RETURNS DECIMAL(10, 2)
AS
$$
SELECT s.amount * (1 - pct);
$$ LANGUAGE sql
    IMMUTABLE
    EXECUTE ON MASTER;

To pass the entire row to the function, use the asterisk (*) — sales.*:

SELECT id,
       category,
       amount,
       get_discount_amount(sales.*, 0.1) AS discounted_amount
FROM sales;

The result is as follows:

 id |  category   | amount | discounted_amount
----+-------------+--------+-------------------
  3 | Books       | 150.00 |           135.000
  4 | Furniture   | 300.00 |           270.000
  1 | Books       | 100.00 |            90.000
  2 | Electronics | 200.00 |           180.000
(4 rows)

SQL functions in Greengage DB can return a table using the RETURNS TABLE clause. This explicitly declares the output columns and allows the function to return multiple rows, like a query result.

This function returns all sales rows with discounted amounts:

CREATE FUNCTION sales_with_discounted_amount(pct DECIMAL)
    RETURNS TABLE
            (
                id       INT,
                date     DATE,
                amount   DECIMAL(10, 2)
            )
AS
$$
SELECT id,
       date,
       amount * (1 - pct)
FROM sales;
$$ LANGUAGE sql
    STABLE
    EXECUTE ON MASTER;

Call the function with a 15% discount:

SELECT *
FROM sales_with_discounted_amount(0.15);

The result should look like this:

 id |    date    |  amount
----+------------+----------
  1 | 2025-08-01 |  85.0000
  2 | 2025-08-02 | 170.0000
  3 | 2025-08-03 | 127.5000
  4 | 2025-08-03 | 255.0000
(4 rows)

SQL functions in Greengage DB can return multiple rows using the SETOF keyword. This returns rows of a specific type (a table, composite type, or base type) and allows the function to behave like a table, producing one or more rows per call.

Unlike RETURNS TABLE, which explicitly declares output columns in the function signature, RETURNS SETOF uses an existing type definition for its output.

The function below returns all sales rows with discounted amounts:

CREATE FUNCTION sales_with_discounted_amount(pct DECIMAL)
    RETURNS SETOF sales
AS
$$
SELECT id,
       date,
       amount * (1 - pct),
       category
FROM sales;
$$ LANGUAGE sql
    STABLE
    EXECUTE ON MASTER;

Call the function with a 20% discount:

SELECT *
FROM sales_with_discounted_amount(0.2);

The result should look like this:

 id |    date    | amount  |  category
----+------------+---------+-------------
  3 | 2025-08-03 | 120.000 | Books
  4 | 2025-08-03 | 240.000 | Furniture
  1 | 2025-08-01 |  80.000 | Books
  2 | 2025-08-02 | 160.000 | Electronics
(4 rows)

You can execute SQL functions directly on segment instances. The example below defines a function that returns the IP address and port of each segment:

CREATE OR REPLACE FUNCTION get_segment_address()
    RETURNS SETOF TEXT
AS
$$
SELECT 'IP: ' || inet_server_addr()::text || ', port: ' || inet_server_port()::text;
$$ LANGUAGE sql
    VOLATILE
    EXECUTE ON ALL SEGMENTS;

Call the function to see the addresses of all segments:

SELECT *
FROM get_segment_address();

The output may look like this:

       get_segment_address
----------------------------------
 IP: 192.168.1.30/32, port: 10000
 IP: 192.168.1.40/32, port: 10001
 IP: 192.168.1.30/32, port: 10001
 IP: 192.168.1.40/32, port: 10000
(4 rows)

Greengage DB lets you create user-defined functions in languages other than SQL or C. These languages are called procedural languages (PLs). Greengage DB supports several procedural languages, including PL/pgSQL, PL/Python, and PL/Java.

PL/pgSQL functions allow you to write procedural logic in Greengage DB, including conditional statements, loops, and variable assignments. The example below categorizes a sales amount as Low, Medium, or High:

CREATE FUNCTION categorize_amount(amount DECIMAL)
    RETURNS TEXT
AS
$$
BEGIN
    IF amount <= 100 THEN
        RETURN 'Low';
    ELSIF amount <= 200 THEN
        RETURN 'Medium';
    ELSE
        RETURN 'High';
    END IF;
END;
$$ LANGUAGE plpgsql;

Call the function on the sales table:

SELECT id,
       amount,
       categorize_amount(amount) AS category
FROM sales
ORDER BY id;

The result should look like this:

 id | amount | category
----+--------+----------
  1 | 100.00 | Low
  2 | 200.00 | Medium
  3 | 150.00 | Medium
  4 | 300.00 | High
(4 rows)

PL/Python functions let you use Python syntax and libraries within the database.

Enable the PL/Python language:

CREATE EXTENSION plpythonu;

Create a function that categorizes sales amounts using Python:

CREATE FUNCTION categorize_amount(amount DECIMAL)
    RETURNS TEXT
AS
$$
    if amount <= 100:
        return 'Low'
    elif amount <= 200:
        return 'Medium'
    else:
        return 'High'
$$ LANGUAGE plpythonu
    IMMUTABLE;

Call the function on the sales table:

SELECT id,
       amount,
       categorize_amount(amount) AS category
FROM sales
ORDER BY id;

The expected result is:

 id | amount | category
----+--------+----------
  1 | 100.00 | Low
  2 | 200.00 | Medium
  3 | 150.00 | Medium
  4 | 300.00 | High
(4 rows)

Clean up by removing the PL/Python extension if it’s no longer needed:

DROP EXTENSION plpythonu;

You can use psql meta-commands to inspect functions in Greengage DB.

List all functions in the current database:

\df

Example output:

                                         List of functions
 Schema |             Name             | Result data type |      Argument data types       |  Type
--------+------------------------------+------------------+--------------------------------+--------
 public | add_numbers                  | integer          | integer, integer               | normal
 public | calculate_stats              | stats_result     | a numeric, b numeric           | normal
 public | get_discount_amount          | numeric          | s sales, pct numeric           | normal
 public | get_segment_address          | SETOF text       |                                | normal
 public | greet_user                   | text             | msg text DEFAULT 'world'::text | normal
 public | multiply_numbers             | integer          | a integer, b integer           | normal
 public | sales_with_discounted_amount | SETOF sales      | pct numeric                    | normal
 public | subtract_numbers             | anyelement       | a anyelement, b anyelement     | normal
 public | sum_numbers                  | bigint           | VARIADIC nums integer[]        | normal
(9 rows)

View information about a specific function:

\df add_numbers

The result might look like this:

                           List of functions
 Schema |    Name     | Result data type | Argument data types |  Type
--------+-------------+------------------+---------------------+--------
 public | add_numbers | integer          | integer, integer    | normal
(1 row)

Display the function definition:

\sf add_numbers

The result might look as follows:

CREATE OR REPLACE FUNCTION public.add_numbers(integer, integer)
 RETURNS integer
 LANGUAGE sql
 IMMUTABLE
AS $function$
SELECT $1 + $2;
$function$

You can query the pg_proc system catalog to inspect function properties such as volatility and execution location, for example:

SELECT nspname         AS schema,
       proname         AS function_name,
       provolatile     AS volatility,
       proexeclocation AS execute_on
FROM pg_proc
         JOIN pg_namespace ON pg_namespace.oid = pg_proc.pronamespace
WHERE nspname NOT IN ('pg_catalog', 'information_schema', 'gp_toolkit')
ORDER BY schema, proname;

The result might look like this:

 schema |        function_name         | volatility | execute_on
--------+------------------------------+------------+------------
 public | add_numbers                  | i          | a
 public | calculate_stats              | i          | a
 public | get_discount_amount          | i          | m
 public | get_segment_address          | v          | s
 public | greet_user                   | i          | m
 public | multiply_numbers             | i          | a
 public | sales_with_discounted_amount | s          | m
 public | subtract_numbers             | i          | a
 public | sum_numbers                  | i          | a
(9 rows)

where:

volatility codes: i = IMMUTABLE, s = STABLE, v = VOLATILE.
execute_on codes: a = ANY, m = MASTER, s = ALL SEGMENTS.

To drop a function, use the DROP FUNCTION command:

DROP FUNCTION add_numbers(INT, INT);

To drop a function along with related objects, such as operators, add the CASCADE option.

Views and materialized views

Data manipulation

User-defined functions (UDF)

Overview

Types of functions

Create a function

Alter functions

Function volatility

EXECUTE ON attributes

Functions on replicated tables

Examples: query language (SQL) functions

Reference arguments: numbers or names

Polymorphic arguments

Variable number of arguments

Output parameters

Composite types

Functions returning and using tables

Execute functions on segments

Examples: procedural language functions

PL/pgSQL

PL/Python

View information about functions

psql meta-commands

System catalogs

Drop functions