23.10. Examples

Example 23-2. A Simple PL/pgSQL Function to Increment an Integer

The following two PL/pgSQL functions are identical to their counterparts from the C language function discussion. This function receives an integer and increments it by one, returning the incremented value.

CREATE FUNCTION add_one (integer) RETURNS INTEGER AS '
    BEGIN
        RETURN $1 + 1;
    END;
' LANGUAGE 'plpgsql';

Example 23-3. A Simple PL/pgSQL Function to Concatenate Text

This function receives two text parameters and returns the result of concatenating them.

CREATE FUNCTION concat_text (TEXT, TEXT) RETURNS TEXT AS '
    BEGIN
        RETURN $1 || $2;
    END;
' LANGUAGE 'plpgsql';

Example 23-4. A PL/pgSQL Function on Composite Type

In this example, we take EMP (a table) and an integer as arguments to our function, which returns a boolean. If the salary field of the EMP table is NULL, we return f. Otherwise we compare with that field with the integer passed to the function and return the boolean result of the comparison (t or f). This is the PL/pgSQL equivalent to the example from the C functions.

CREATE FUNCTION c_overpaid (EMP, INTEGER) RETURNS BOOLEAN AS '
    DECLARE
        emprec ALIAS FOR $1;
        sallim ALIAS FOR $2;
    BEGIN
        IF emprec.salary ISNULL THEN
            RETURN ''f'';
        END IF;
        RETURN emprec.salary > sallim;
    END;
' LANGUAGE 'plpgsql';