ruk·si
/notes

🐘 PostgreSQL
Functions

Updated at 2015-09-26 18:06

PostgreSQL provides four kinds of functions:

  1. Query language functions, written in SQL.
  2. Procedural language functions, e.g. written in PL/pgSQL or PL/Tcl.
  3. Internal functions, usually written in C, statically linked to the server.
  4. C-language functions.

All functions have volatility: VOLATILE, STABLE, or IMMUTABLE.

  • VOLATILE function can do anything, including modifying the database. This the default setting.
  • STABLE function cannot modify the database and is guaranteed to return the same results given the same arguments for all rows within a single statement.
  • IMMUTABLE function cannot modify the database and is guaranteed to return the same results given the same arguments forever.

Functions can also be STRICT, meaning that if any of parameters is NULL, the result is NULL.

Functions also have a cost:

  • A positive integer giving estimated execution cost.
  • Units are cpu_operator_cost
  • If function returns a set, cost is per row returned.
  • If cost is not specified: 1 unit for C-language, 100 units all other.
  • Larger values causes planner to avoid evaluating the function, if possible.

Query Language Functions

Query language functions return last thing that was selected.

CREATE FUNCTION debit_transfer(account_id integer, debit numeric)
RETURNS integer AS
$function$

    UPDATE bank
       SET balance = (balance - debit)
     WHERE account_id = debit_transfer.account_id;

    SELECT balance
      FROM bank
     WHERE account_id = debit_transfer.account_id;

$function$
LANGUAGE SQL;

-- Usage:
SELECT debit_transfer(17, 100.0);

You can also use shorter form RETURNING.

CREATE FUNCTION debit_transfer(account_id integer, debit numeric)
RETURNS integer AS
$function$

    UPDATE bank
       SET balance = (balance - debit)
     WHERE account_id = debit_transfer.account_id

    RETURNING balance;

$function$
LANGUAGE SQL;

Functions on composite types.

CREATE TABLE employee (
    name    text,
    salary  numeric,
    age     integer,
    cubicle point
);
INSERT INTO employee VALUES ('Bill', 4200, 45, '(2,1)');

CREATE FUNCTION double_salary(employee)
RETURNS numeric AS
$function$

    SELECT $1.salary * 2 AS salary;

$function$
LANGUAGE SQL;

-- Usage:
SELECT
      name,
      double_salary(employee.*) AS dream
 FROM employee
WHERE employee.cubicle ~= point '(2,1)';

Returning composite types.

CREATE FUNCTION new_employee()
RETURNS employee AS
$function$

    SELECT
        text 'None'   AS name,
        1000.0        AS salary,
        25            AS age,
        point '(2,2)' AS cubicle;

$function$
LANGUAGE SQL;

-- Or optionally

CREATE FUNCTION new_employee()
RETURNS employee AS
$function$
    SELECT ROW('None', 1000.0, 25, '(2,2)')::employee;
$function$ LANGUAGE SQL;

You can specify output parameters in the function creation.

CREATE FUNCTION sum_n_product(
    x           int,
    y           int,
    OUT sum     int,
    OUT product int
)
AS '
    SELECT
        x + y,
        x * y
'
LANGUAGE SQL;

-- Usage:
SELECT *
FROM sum_n_product(11, 42);
--  sum | product
--------+---------
--  53  |     462
-- (1 row)

Variable number of arguments in function calls.

CREATE FUNCTION my_least(VARIADIC arr numeric[])
RETURNS numeric
AS $function$
    SELECT min($1[i])
      FROM generate_subscripts($1, 1) g(i);
$function$
LANGUAGE SQL;

-- Usage:
SELECT my_least(10, -1, 5, 4.4);
-- Returns -1.

-- Calling with premade arguments array.
SELECT my_least(VARIADIC ARRAY[10, -1, 5, 4.4]);
-- Returns -1.

Default values in function calls.

CREATE FUNCTION foo(
    a int,
    b int DEFAULT 2,
    c int DEFAULT 3
)
RETURNS int AS
$function$
    SELECT $1 + $2 + $3;
$function$
LANGUAGE SQL;

SELECT foo(10, 20, 30);
-- Returns 60.

SELECT foo(10, 20);
-- Returns 33.

SELECT foo(10);
-- Returns 15.

SELECT foo();
-- Fails since there is no default for the first argument

Better converters.

CREATE OR REPLACE FUNCTION timestamp_to_epoch(time timestamp)
RETURNS integer AS
$function$
    SELECT extract(epoch FROM $1)::integer
$function$
LANGUAGE sql;

CREATE OR REPLACE FUNCTION epoch_to_timestamp(epoch integer)
RETURNS timestamp AS
$function$
    SELECT to_timestamp($1)::timestamp
$function$
LANGUAGE sql;

Procedural Language Functions

CREATE OR REPLACE FUNCTION check_product_in(
    product_id   text,
    order_number integer,
    is_save      boolean
)
RETURNS SETOF my_reponse_row AS
$function$
DECLARE
    -- Declaring variables.
    order_data order%ROWTYPE;
    was_saved boolean;
    can_be_sold CONSTANT integer := 1;
    must_be_sold CONSTANT integer := 2;
BEGIN

    response.product_id    := product_id;
    response.order_number  := order_number;
    response.is_save       := is_save;
    response.error_message := '';

    -- Guardian statements.
    IF product_id IS NULL THEN
        response.error_message := 'Product ID is missing.';
        RETURN NEXT response;
        RETURN;
    END IF;

    -- Saving query as parameter.
    SELECT * INTO the_product
      FROM product
     WHERE product.product_id = response.product_id;

    -- If a row exists.
    IF NOT FOUND THEN
        response.error_message := 'Product does not exist.';
        RETURN NEXT response;
        RETURN;
    END IF;

    -- Switch-case style condition.
    IF the_product.type_id NOT IN (can_be_sold, must_be_sold) THEN
        response.error_message := 'Products type id '
            || the_product.type_id
            || ' does not allow selling it.';
        RETURN NEXT response;
        RETURN;
    END IF;

    -- Looping
    total_price = 0;
    FOR value_one_to_use, value_two_to_use IN (
        SELECT pp.price, pp.tax
          FROM product_price pp
    )
    LOOP
        total_price = total_price + (value_one_to_use * value_two_to_use)
    END LOOP;

    RETURN NEXT response;
    RETURN;

END;
$function$
LANGUAGE plpgsql
VOLATILE
COST 100;

COMMENT ON FUNCTION check_product_in(
    text,
    integer,
    boolean
) IS 'This function is really important!';

Sources