CREATE FUNCTION name ( [ ftype [, ...] ] )
RETURNS rtype
AS definition
LANGUAGE 'langname'
[ WITH ( attribute [, ...] ) ]
CREATE FUNCTION name ( [ ftype [, ...] ] )
RETURNS rtype
AS obj_file , link_symbol
LANGUAGE 'langname'
[ WITH ( attribute [, ...] ) ]
Òª´´½¨µÄº¯ÊýÃû£®
º¯Êý²ÎÊýµÄÊý¾ÝÀàÐÍ£®ÊäÈëÀàÐÍ¿ÉÒÔÊÇ»ù±¾ÀàÐÍ£¬×éºÏÀàÐÍ»òÕß opaque¡£ Opaque ±íÃ÷¸Ãº¯Êý½ÓÊÜÒ»¸ö·Ç SQL ÀàÐÍ£¬±ÈÈçÏó char * ÕâÑùµÄÀàÐÍ¡£
·µ»ØÊý¾ÝÀàÐÍ£®ÊäÈëÀàÐÍ¿ÉÒÔÊÇ»ù±¾ÀàÐÍ£¬×éºÏÀàÐÍ£¬ setof type£¬ »òÕß opaque¡£ setof ÐÞÊδʱíʾ¸Ãº¯Êý ½«·µ»ØÒ»Ì×ÌõÄ¿£¬¶ø²»Êǵ¥ÌõÌõÄ¿¡£
Ò»¸ö¹ØÓÚº¯ÊýµÄ¿ÉÑ¡ÐÅÏ¢£¬ÓÃÓÚÓÅ»¯¡£²ÎÔÄÏÂÎÄ»ñȡϸ½Ú£®
Ò»¸ö¶¨Ò庯ÊýµÄ×Ö´®£»Æ京ÒåÈ¡¾öÓÚ£¨Óõģ©ÓïÑÔ¡£ ¿ÉÒÔÊÇÒ»¸öÄÚ²¿º¯ÊýÃû£¬Ò»¸öÖ¸ÏòÒ»¸öÄ¿±êÎļþµÄ·¾¶£¬Ò» ¸öSQL²éѯ»òÕßÒ»ÖÖ¹ý³ÌÓïÑÔµÄÎı¾¡£
ÕâÖÖÐÎʽµÄ AS ×Ó¾äÓÃÓÚ¶¯Ì¬Á´½ÓµÄ C ÓïÑÔº¯Êý£¬ Õâʱ¸Ãº¯ÊýÔÚ C Ô´´úÂëÀïµÄÃû³ÆºÍ SQL º¯ÊýµÄÃû³Æ²»Í¬¡£×Ö·û´® obj_file ÊǺ¬Óпɶ¯Ì¬×°ÔصĶÔÏóµÄÎļþÃû£¬¶ø link_symbolÊǶÔÏóµÄÁ´½Ó·ûºÅ£¬ Õâ¸ö·ûºÅÓë C Ô´´úÂëÀïµÄº¯ÊýÃûÏàͬ¡£
¿ÉÒÔÊÇ 'sql'£ 'C'£¬ 'internal' »ò 'plname'£¬ÕâÀï 'plname' ÊÇËù´´½¨¹ý³ÌµÄÓïÑÔÃû£®²Î¿¼ CREATE LANGUAGE »ñÈ¡ÏêϸÐÅÏ¢£®
CREATE FUNCTION ÔÊÐíÒ»¸ö Postgres Óû§ÔÚÒ»¸öÊý¾Ý¿âÀï×¢²áÒ»¸öº¯Êý£® ²¢ÇÒÕâ¸öÓû§½«±»¿´×÷Õâ¸öº¯ÊýµÄËùÓÐÕߣ®
WITH ×Ó¾äÀï¿ÉÒÔ³öÏÖÏÂÃæµÄÄÚÈÝ£º
iscachable ±íʾ´Ëº¯ÊýÔÚÊäÈë Ïàͬʱ×ÜÊÇ·µ»ØÏàͬµÄÖµ £¨Ò²¾ÍÊÇ˵£¬ Ëü²»×öÊý¾Ý¿â²éÕÒ»òÕßÊÇʹÓÃûÓÐÖ±½ÓÔÚËüµÄ²ÎÊýÁбí³öÏÖµÄÐÅÏ¢£©¡£ ÓÅ»¯Æ÷ʹÓà iscachable À´ÈÏÖª¶Ô¸Ãº¯ÊýµÄµ÷ÓýøÐÐÔ¤ÏȼÆËãÊÇ·ñ°²È«¡£
isstrict ±íÃ÷Èç¹ûËüµÄÈκβÎÊýÊÇ NULL£¬´Ëº¯Êý×ÜÊÇ·µ»Ø NULL£® Èç¹ûÉùÃ÷ÁËÕâ¸öÊôÐÔ£¬ÔòÈç¹û´æÔÚ NULL ²ÎÊýʱ²»»áÖ´Ðиú¯Êý£» ¶øÖ»ÊÇ×Ô¶¯¼ÙÉèÒ»¸ö NULL ½á¹û£®Èç¹ûûÓÐÉùÃ÷ isstrict ¸Ãº¯Êý½«Îª NULL ÊäÈëµ÷Óò¢½øÐд¦Àí£® ÄÇôʣϵÄʾÍÊǺ¯Êý×÷ÕßµÄÔðÈÎÀ´¼ì²é NULL ÊÇ·ñ±ØÐë²¢ÇÒ×öÏàÓ¦ÏìÓ¦£®
Çë²ÎÔÄ PostgreSQL ³ÌÐòÔ±ÊÖ²á ¹ØÓÚͨ¹ýº¯ÊýÀ©Õ¹ Postgres µÄÕ½ڻñÈ¡¸ü¶à¹ØÓÚÊéдÍⲿº¯ÊýµÄÐÅÏ¢£®
ʹÓà DROP FUNCTION ɾ³ýÒ»¸öÓû§¶¨ÒåµÄº¯Êý£®
ÎÒÃÇÔÊÐíÄ㽫ÍêÕûµÄ SQL92 ÀàÐÍÓï·¨ÓÃÓÚ ÊäÈë²ÎÊýºÍ·µ»ØÖµ£®²»¹ý£¬ÓÐЩÀàÐÍÉùÃ÷µÄϸ½Ú£¨±ÈÈ磬 numeric ÀàÐ͵ľ«¶ÈÓò£©ÊÇÓÉϲ㺯ÊýʵÏÖ¸ºÔðµÄ£¬ ²¢Çһᱻ CREATE FUNCTION ÃüÁîÇÄÇĵØÍ̵ô£® £¨Ò²¾ÍÊÇ˵£¬²»ÔÙ±»Ê¶±ð»òÇ¿ÖÆ£©£®
Postgres ÔÊÐíº¯Êý¡°ÖØÔØ¡±£» Ò²¾ÍÊÇ˵£¬Í¬Ò»¸öº¯ÊýÃû¿ÉÒÔÓÃÓÚ¼¸¸ö²»Í¬µÄº¯Êý£¬ Ö»ÒªËüÃǵIJÎÊý¿ÉÒÔÇø·ÖËüÃÇ¡£²»¹ý£¬Õâ¸ö¹¦ÄÜÔÚÓÃÓÚ internal ºÍ C ÓïÑÔ µÄº¯ÊýʱҪСÐÄ¡£
Á½¸ö internal º¯ÊýÓµÓÐÏàͬ C Ãû³Æʱ¿Ï¶¨»á·¢ÉúÁ´½Óʱ´íÎó¡£ Òª½â¾öÕâ¸öÎÊÌ⣬¸øËüÃǸ³Ó費ͬµÄ C Ãû³Æ£¨ÀýÈ磬ʹÓòÎÊýÀà ÐÍ×öΪ C Ãû³ÆµÄÒ»²¿·Ö£©£¬È»ºóÔÚ CREATE FUNCTION µÄ AS ×Ó¾äÀïÃæÉùÃ÷ÕâЩÃû×Ö¡£ Èç¹û AS ×Ó¾äΪ¿Õ£¬ÄÇô CREATE FUNCTION ¼ÙÉ躯ÊýµÄ C Ãû³ÆÓëSQLÃû³ÆÒ»Ñù¡£
ÀàËƵأ¬Èç¹ûÓöà¸ö C ÓïÑÔº¯ÊýÖØÔØ SQL º¯Êý£¬ ¸øÿ¸ö C ÓïÑÔº¯ÊýµÄʵÀýÒ»¸ö¶ÀÁ¢µÄÃû³Æ£¬²¢ÇÒʹÓà CREATE FUNCTION Óï·¨ÀïµÄ AS ¾äµÄ²»Í¬ÐÎʽÀ´È·±£ÖØÔØµÄ SQL º¯ÊýÃû³ÆÕýÈ·µØ½âÊÍΪÏàÓ¦¶¯Ì¬Á´½Ó¶ÔÏó¡£
´´½¨Ò»¸ö¼òµ¥µÄ SQL º¯Êý£º
CREATE FUNCTION one() RETURNS int4
AS 'SELECT 1 AS RESULT'
LANGUAGE 'sql';
SELECT one() AS answer;
answer
--------
1
Õâ¸öÀý×Óͨ¹ýµ÷ÓÃÒ»¸öÓû§´´½¨µÄ¹²Ïí¿âµÄ·¾¶´´½¨Ò»¸ö C º¯Êý£® ¸Ã·¾¶¼ÆËãÒ»¸ö¼ì²âλ²¢ÇÒÈç¹ûº¯Êý²ÎÊýÀïµÄ¼ì²âλ ÕýÈ·¾Í·µ»ØÒ»¸ö TRUE £®ÕâЩÊÇͨ¹ýʹÓÃÒ»¸ö CHECK Ô¼ÊøʵÏֵģ®
CREATE FUNCTION ean_checkdigit(bpchar, bpchar) RETURNS boolean
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))
);
Õâ¸öÀý×Ó´´½¨Ò»¸öÔÚÓû§¶¨ÒåÀàÐÍ complex ºÍÄÚ²¿ÀàÐÍ point Ö®¼ä×öÀàÐÍת»»µÄº¯Êý¡£¸Ãº¯ÊýÊÇÓÃÒ»¸ö´Ó C Ô´´úÂë±àÒëµÄ ¶¯Ì¬×°ÔصĶÔÏóÀ´ÊµÏֵġ£¶ÔÓÚ Postgres ¶øÑÔ£¬ Òª×Ô¶¯Ñ°ÕÒÀàÐÍת»»º¯Êý£¬sql º¯Êý±ØÐëºÍ·µ»ØÀàÐÍͬÃû£¬ Òò¶øÖØÔØÊDz»¿É±ÜÃâµÄ¡£ ¸Ãº¯ÊýÃûͨ¹ýʹÓà SQL¶¨ÒåÀï AS ×Ó¾äµÄµÚ¶þÖÖÀàÐÍÀ´ÖØÔØ£º
CREATE FUNCTION point(complex) RETURNS point
AS '/home/bernie/pgsql/lib/complex.so', 'complex_to_point'
LANGUAGE 'c';
¸Ãº¯ÊýµÄ C ¶¨ÒåÊÇ£º
Point * complex_to_point (Complex *z)
{
Point *p;
p = (Point *) palloc(sizeof(Point));
p->x = z->x;
p->y = z->y;
return p;
}