CREATE FUNCTION
SQL - Language Statements
CREATE FUNCTION
Defines a new function
1998-04-15
CREATE FUNCTION name ([ftype [, ...]])
RETURNS rtype
AS path
LANGUAGE 'langname'
1998-04-15
Inputs
name
The name of a function to create.
ftype
The data type of function arguments.
rtype
The return data type.
path
May be either an SQL-query or an absolute path to an
object file.
langname
may be 'c', 'sql', 'internal' or 'plname'.
(where 'plname' is the language name of a created procedural
language. See CREATE LANGUAGE for details).
1998-04-15
Outputs
CREATE
This is returned if the command completes successfully.
1998-04-15
Description
With this command, a PostgreSQL user can register a function
with PostgreSQL. Subsequently, this user is treated as the
owner of the function.
1998-04-15
Notes
Refer to PostgreSQL User's Guide chapter 6 for further information.
This reference needs to be corrected.
Refer to the
DROP FUNCTION
statement to drop functions.
Usage
To create a simple SQL function:
CREATE FUNCTION one() RETURNS int4
AS 'SELECT 1 AS RESULT'
LANGUAGE 'sql';
SELECT one() AS answer;
answer
------
1
To create a C function, calling a routine from a user-created
shared library. This particular routine calculates a check
digit and returns TRUE if the check digit in the function parameters
is correct. It is intended for use in a CHECK contraint.
CREATE FUNCTION ean_checkdigit(bpchar, bpchar) RETURNS bool
AS '/usr1/proj/bray/sql/funcs.so' LANGUAGE 'c';
CREATE TABLE product
(
id char(8) PRIMARY KEY,
eanprefix char(8) CHECK (eanprefix ~ '[0-9]{2}-[0-9]{5}')
REFERENCES brandname(ean_prefix),
eancode char(6) CHECK (eancode ~ '[0-9]{6}'),
CONSTRAINT ean CHECK (ean_checkdigit(eanprefix, eancode))
);
Bugs
A C function cannot return a set of values.
Compatibility
The CREATE FUNCTION statement is a PostgreSQL language extension.
1998-04-15
SQL/PSM<footnote>
<para>
PSM stands for Persistent Stored Modules, it is a procedural
language and it was originally hoped that PSM would be ratified
as an official standard by late 1996. However PSM will
eventually become a standard.
</para>
</footnote>
The SQL/PSM CREATE FUNCTION statement has the following syntax:
CREATE FUNCTION name
( [ [IN|OUT|INOUT] parm type [, ...] ])
RETURNS rtype
LANGUAGE 'langname'
ESPECIFIC routine
SQL-statement