CREATE FUNCTION
SQL - Language Statements
CREATE FUNCTION
Defines a new function
1998-09-09
CREATE FUNCTION name ( [ ftype [, ...] ] )
RETURNS rtype
AS path
LANGUAGE 'langname'
1998-09-09
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 name of a created procedural
language. See CREATE LANGUAGE for details.
1998-09-09
Outputs
status
CREATE
This is returned if the command completes successfully.
1998-09-09
Description
CREATE FUNCTION allows a
Postgres user
to register a function
with a database. Subsequently, this user is treated as the
owner of the function.
1998-09-09
Notes
Refer to the chapter on functions
in the PostgreSQL Programmer's Guide
for further information.
Use DROP FUNCTION
to drop user-defined 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
CREATE FUNCTION is
a Postgres language extension.
1998-09-09
SQL/PSM
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. As of mid-1998, this
has not yet happened, but it is hoped that PSM will
eventually become a standard.
SQL/PSM CREATE FUNCTION has the following syntax:
CREATE FUNCTION name
( [ [ IN | OUT | INOUT ] parm type [, ...] ] )
RETURNS rtype
LANGUAGE 'langname'
ESPECIFIC routine
SQL-statement