Managing UDFs

Create, query, modify, and delete User-Defined Functions in Tacnode.

Creating Functions

Use CREATE FUNCTION to define a new UDF:

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
AS $$
  return a + b
$$ LANGUAGE plpython3u;

Key options:

• OR REPLACE — Update the function if it already exists
• LANGUAGE — Choose plpython3u, plpythonvec3u, or plpgsql

See the PL/Python guide or PL/pgSQL guide for language-specific details.

Listing Functions

Quick List with psql

\df              -- List all functions
\df+ my_function -- Show function details including source code

Query All Custom Functions

SELECT
    proname AS name,
    pg_get_function_arguments(oid) AS arguments,
    pg_get_function_result(oid) AS returns,
    lanname AS language
FROM pg_proc p
JOIN pg_language l ON p.prolang = l.oid
WHERE pronamespace = 'public'::regnamespace
ORDER BY proname;

View Function Source Code

SELECT pg_get_functiondef(oid)
FROM pg_proc
WHERE proname = 'add_numbers';

Find All PL/Python Functions

SELECT
    proname AS name,
    pg_get_function_arguments(oid) AS arguments,
    prosrc AS source
FROM pg_proc p
JOIN pg_language l ON p.prolang = l.oid
WHERE l.lanname = 'plpython3u'
ORDER BY proname;

Find Functions Using a Library

-- Find functions that import numpy
SELECT proname
FROM pg_proc
WHERE prosrc LIKE '%import numpy%'
   OR prosrc LIKE '%from numpy%';

Dropping Functions

-- Drop a function (must specify parameter types)
DROP FUNCTION add_numbers(INTEGER, INTEGER);

-- Drop if exists (no error if function doesn't exist)
DROP FUNCTION IF EXISTS add_numbers(INTEGER, INTEGER);

When functions are overloaded (same name, different parameters), you must specify which one to drop:

-- Two functions with the same name
CREATE FUNCTION format_value(val TEXT) RETURNS TEXT ...
CREATE FUNCTION format_value(val INTEGER) RETURNS TEXT ...

-- Drop the TEXT version
DROP FUNCTION format_value(TEXT);

-- Drop the INTEGER version
DROP FUNCTION format_value(INTEGER);

Bulk Delete by Language

-- Delete all PL/Python functions in public schema
DO $$
DECLARE
    func RECORD;
BEGIN
    FOR func IN
        SELECT proname, pg_get_function_identity_arguments(p.oid) AS args
        FROM pg_proc p
        JOIN pg_language l ON p.prolang = l.oid
        WHERE l.lanname = 'plpython3u'
          AND pronamespace = 'public'::regnamespace
    LOOP
        EXECUTE format('DROP FUNCTION %I(%s)', func.proname, func.args);
    END LOOP;
END $$;

Modifying Functions

Update Function Body

Use CREATE OR REPLACE to update an existing function:

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
AS $$
    # Updated implementation
    result = a + b
    return result
$$ LANGUAGE plpython3u;

Change Owner

ALTER FUNCTION add_numbers(INTEGER, INTEGER) OWNER TO new_owner;

Add a Comment

COMMENT ON FUNCTION add_numbers(INTEGER, INTEGER)
IS 'Adds two integers and returns the sum';

Permissions

View Function Permissions

SELECT proname, proacl
FROM pg_proc
WHERE proname = 'add_numbers';

Grant Execute Permission

-- Grant to a user
GRANT EXECUTE ON FUNCTION add_numbers(INTEGER, INTEGER) TO alice;

-- Grant to a role
GRANT EXECUTE ON FUNCTION add_numbers(INTEGER, INTEGER) TO analysts;

-- Grant to everyone
GRANT EXECUTE ON FUNCTION add_numbers(INTEGER, INTEGER) TO PUBLIC;

Revoke Permission

REVOKE EXECUTE ON FUNCTION add_numbers(INTEGER, INTEGER) FROM alice;

Function Attributes

Volatility

Tell the query planner how your function behaves:

-- Can return different results each time (default)
CREATE FUNCTION get_random() RETURNS INTEGER
AS $$ import random; return random.randint(1, 100) $$
LANGUAGE plpython3u VOLATILE;

-- Same result within a single query
CREATE FUNCTION get_config(key TEXT) RETURNS TEXT
AS $$ ... $$
LANGUAGE plpython3u STABLE;

-- Always same result for same inputs
CREATE FUNCTION calculate_tax(amount NUMERIC) RETURNS NUMERIC
AS $$ return float(amount) * 0.08 $$
LANGUAGE plpython3u IMMUTABLE;

STRICT (Returns NULL on NULL Input)

Skip null-checking boilerplate:

CREATE FUNCTION double_it(n INTEGER)
RETURNS INTEGER
AS $$ return n * 2 $$
LANGUAGE plpython3u STRICT;

SELECT double_it(NULL);  -- Returns NULL, function never executes

SECURITY DEFINER

Run with the permissions of the function owner instead of the caller:

CREATE FUNCTION admin_action()
RETURNS TEXT
AS $$
BEGIN
    -- This runs with owner's permissions
    RETURN 'done';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;