you are at >> pgSQL >>functions
functions
At PL / pgsql language you can define functions that can be used inside PostgreSQL database. To define your own function, use the CREATE FUNCTION command.General form of definition of the function
CREATE FUNCTION function_name ( [arg_type1 [,arg_type2 [,...]]] )
RETURNS result_type
AS 'BEGIN function_body
END;'
LANGUAGE 'language_name'
Example : Create function which will be returning surname of given employee id from `employees` table
CREATE FUNCTION employee_surname ( int4 )
RETURNS text
AS' BEGIN
DECLARE n;
SELECT `surname` FROM `employees` WHERE `id`=$1;
RETURN n;
END;'
LANGUAGE 'language_name'
When a function is invoked for the first time, the definition of the function is compiled to executable form, and then carried out. We can detect any errors only by using this function.
There is also possible overloading of function names, because PostgreSQL considers different functions if there number of arguments and / or type of arguments are different.
Inside function, you can declare local variables. Each variable must have a specific type, which may be one of the built-in PostgreSQL type, type of user or type of the corresponding row in the table. Declarations of variables for the function are recorded in the DECLARE section or at the function block . Variables declared in a block are visible only inside this block and in other blocks which are part of this main block. The variable declared in the internal block , with the same name as the variable outside the block, overrides external variable.
[ wróć na górę strony ]