Contents /
Previous /
Next
Server-Side Functions (Stored Procedures)
Server-side functions (also called stored procedures)
allow programs to be embedded into the database.
These programs can be accessed from client applications and
used in database queries. Moving code into the server allows for
increased efficiency, maintainability, and consistency.
Creating Custom Functions
Server-side functions can be written in several languages:
SQL/PGSQL, Perl, C, etc.
Functions are created with CREATE FUNCTION and removed
with DROP FUNCTION.
<![CDATA[
CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] )
RETURNS rettype
AS 'definition'
LANGUAGE langname
DROP FUNCTION name ( [ argtype [, ...] ] )
]]>
CREATE FUNCTION defines a new function. CREATE OR REPLACE FUNCTION
will either create a new function, or replace an existing definition.
argtype are the data type(s) of the function's arguments, if any.
The input types may be base or complex types, opaque, or the same as the type of an
existing column. Opaque indicates that the function accepts arguments
of a non-SQL type such as char *.
rettype is the return data type.
langname may be "SQL", "C", internal or "PLPGSQL"
for a SQL procedural language.
Example "SQL language function":
<![CDATA[
CREATE OR REPLACE FUNCTION remaining_years ( FLOAT )
RETURNS FLOAT
AS 'SELECT ( (6.0 - $1) ) / 2.0'
LANGUAGE 'sql' ;
SELECT remaining_years (3.0) ;
remaining_years
-----------------
1.5
SELECT name, remaining_years(semester) FROM stud;
name | remaining_years
------+-----------------
fred | 2
tom | 3
john | 2
lisa | 2
DROP FUNCTION remaining_years( FLOAT ) ;
]]>
The $1 in the SELECT is automatically replaced by the first argument
of the function call. If a second argument were present, it would be
represented as $2.
SQL Procedural Language (PL/PGSQL) Functions
PL/PGSQL is a true programming language intended for server-side functions.
While SQL functions allow only argument
substitution, PL/PGSQL includes features such as variables,
conditional evaluation, and looping.
PL/PGSQL is not installed in each database by default. To use it in
database test, you must install it with the
CREATE LANGUAGE command or by running the shell program:
<![CDATA[
createlang plpgsql DB_name
]]>
Example "get max" (first execute in a unix shell: createlang plpgsql workshop):
<![CDATA[
CREATE OR REPLACE FUNCTION get_max( INT, INT)
RETURNS INT
AS 'DECLARE tmp_max INT;
BEGIN
tmp_max = $1;
IF $1 < $2 THEN
tmp_max = $2;
END IF;
RETURN tmp_max;
END;' LANGUAGE 'plpgsql' ;
SELECT get_max(7, 4) ;
get_max
---------
7
(1 row)
]]>
DECLARE defines variables used in the function.
RETURN exits and returns a value from the function.
FOR Loops
FOR can create a loop that iterates over a range of integer values:
<![CDATA[
FOR name IN [ REVERSE ] expression .. expression LOOP
statements
END LOOP;
]]>
or the FOR LOOP
can iterate through the results of a query:
<![CDATA[
FOR record | row IN select_query LOOP
statements
END LOOP;
]]>
Example "Looping Through Query Results":
<![CDATA[
CREATE OR REPLACE FUNCTION get_max_semester()
RETURNS INT AS 'DECLARE table_row RECORD; tmp_max INT;
BEGIN
tmp_max = 0;
FOR table_row IN SELECT semester FROM stud
LOOP
IF tmp_max < table_row.semester THEN
tmp_max = table_row.semester;
END IF;
END LOOP;
RETURN tmp_max;
END;'
LANGUAGE 'plpgsql' ;
SELECT get_max_semester() ;
get_max_semester
------------------
3
(1 row)
]]>
Rowtypes:
User-defined attributes of a table row are accessible in a rowtype variable
(var_name tablename%ROWTYPE;).
Records:
Record variables are similar to rowtype variables, but they have no
predefined structure (name RECORD;), you can use them to access any
type of row.
See the PostgreSQL Programer's Guide for more information
on PL/PGSQL.
Extending POSTGRESQL Using C ( Embedded C, ECPG )
Extending POSTGRESQL with C functions involves the following steps:
- Write C code to implement the new functionality.
- Compile the C code into an object file that contains CPU instructions.
- Issue CREATE FUNCTION commands to register the new functions.