CREATE FUNCTION

中文man手册

目录

NAME CREATE FUNCTION
SYNOPSIS .sp .nf CREATE [ OR REPLACE ] FUNCTION
DESCRIPTION 描述
PARAMETERS 参数
NOTES 注意
EXAMPLES 例子
COMPATIBILITY 兼容性
SEE ALSO 参见
译者
跋

CREATE FUNCTION

NAME
SYNOPSIS
DESCRIPTION æè¿°
PARAMETERS åæ°
NOTES æ³¨æ
EXAMPLES ä¾å
COMPATIBILITY å¼å®¹æ§
SEE ALSO åè§
è¯è
è·

NAME

CREATE FUNCTION - å®ä¹ä¸ä¸ªæ°å½æ°

SYNOPSIS

CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] )
RETURNS rettype
{ LANGUAGE langname
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER
| AS ’definition’
| AS ’obj_file’, ’link_symbol’
} ...
[ WITH ( attribute [, ...] ) ]

DESCRIPTION æè¿°

CREATE FUNCTION å®ä¹ä¸ä¸ªæ°çå½æ°ã CREATE OR REPLACE FUNCTION å°è¦ä¹åå»ºä¸ä¸ªæ°å½æ°ï¼ è¦ä¹æ¿æ¢ç°æçå®ä¹ã

å¦æåå«äºä¸ä¸ªæ¨¡å¼åï¼é£ä¹å½æ°å°±å¨æå®çæ¨¡å¼ä¸åå»ºãå¦åå®ä¼å¨å½åæ¨¡å¼ä¸- åå»ºã æ°å½æ°çååä¸è½ååä¸ä¸ªæ¨¡å¼ä¸çä»»ä½å¸¦æåæ ·åæ°ç±»åçå½æ°ååã ä¸è¿ï¼åæ°ç±»åä¸åçå½æ°å¯ä»¥ååï¼è¿å«åéè½½ï¼ã

è¦æ´æ°ä¸ä¸ªç°æå½æ°çå®ä¹ï¼ä½¿ç¨ CREATE OR REPLACE FUNCTIONã æä»¬ä¸è½ç¨è¿ä¸ªæ¹æ³ä¿®æ¹ä¸ä¸ªå½æ°çåå- æèåæ°ç±»åï¼å¦æä½ è¿ä¹å¹²ï¼é£ä¹ä½ å°±ä¼åå»ºä¸ä¸ªæ°çï¼ä¸åçå½æ°ï¼ã åæ ·ï¼CREATE OR REPLACE FUNCTION ä¹ä¸ä¼åè®¸ä½ ä¿®æ¹ä¸ä¸ªç°æå½æ°çè¿åç±»åã è¦åè¿äºäºæï¼ä½ å¿é¡»å é¤å¹¶éæ°åå»ºå½æ°ã

å¦æä½åé¤ç¶åéå»ºä¸ä¸ªå½æ°ï¼æ°å½æ°åæ§çå°æ¯ä¸åçå®ä½ï¼ä½ä¼ç´åç°æè§åï¼è§å¾ï¼è§¦åå¨ç- çã ä½¿ç¨ CREATE OR REPLACE FUNCTION å¯ä»¥å¨ä¸ç ´åå¼ç¨è¯¥å½æ°çå¯¹è±¡çåæä¸ä¿®æ¹å½æ°å®ä¹ã

åå»ºè¿ä¸ªå½æ°çç¨æ·æä¸ºå½æ°çææèã

PARAMETERS åæ°

name

è¦åå»ºçå½æ°ååã

argtype

è¯¥å½æ°çæ°æ®ç±»åï¼å¯ä»¥ææ¨¡å¼ä¿®é¥°ï¼ãå¦ææçè¯ï¼åæ°ç±»åå¯ä»¥æ¯åºæ¬ç±»åï¼ä¹å¯ä»¥æ¯å¤åç±»åï¼åç±»åï¼æèåä¸ä¸ªç°æå- æ®µç¸åçç±»åã

ä¸ä¸ªåæ®µçç±»åæ¯ç¨ tablename.columnname%TYPE è¡¨ç¤ºçï¼ä½¿ç¨è¿ä¸ªä¸è¥¿å¯ä»¥å¸®å©å½æ°ç¬ç«äºè¡¨å®ä¹çä¿®æ¹ã

æ ¹æ®å®ç°è¯è¨çä¸åï¼æä»¬è¿å¯ä»¥å¨è¿ä¸é¢å£°æ "ä¼ªç±»å"ï¼ æ¯å¦ cstringãä¼ªç±»åè¡¨ç¤ºå®éçåæ°ç±»åè¦ä¹æ¯æ²¡æå®æ´å°å£°æï¼è¦ä¹æ¯å¨æ®éç SQL æ°æ®ç±»åä¹å¤ã

rettype

è¿åæ°æ®ç±»åãè¾åºç±»åå¯ä»¥å£°æä¸ºä¸ä¸ªåºæ¬ç±»åï¼å¤åç±»åï¼åç±»åï¼ æèä»ç°æåæ®µæ·è´ãåéä¸é¢ argtype çæè¿°è·åå¦ä½å¼ç¨ä¸ä¸ªç°æç±»åçä¿¡æ¯ã

æ ¹æ®å®ç°è¯è¨çä¸åï¼æä»¬è¿å¯ä»¥å¨è¿ä¸é¢å£°æ "ä¼ªç±»å"ï¼ æ¯å¦ cstringã SETOF ä¿®é¥°è¯è¡¨ç¤ºè¯¥å½æ°å°è¿åä¸å¥æ¡ç®ï¼ èä¸æ¯ä¸æ¡æ¡ç®ã

langname

ç¨ä»¥å®ç°å½æ°çè¯è¨çååã å¯ä»¥æ¯ SQLï¼Cï¼ internalï¼æèæ¯ç¨æ·å®ä¹çè¿ç¨è¯è¨ååã ï¼åè§ createlangã ï¼ ä¸ºäºä¿æåä¸å¼å®¹ï¼è¯¥ååå¯ä»¥ç¨åå¼å·åå´ã

IMMUTABLE

STABLE

VOLATILE

è¿äºå±æ§åè¯ç³»ç»æå¯¹è¯¥å½æ°çå¤æ¬¡è°ç¨æ¿æ¢æä¸æ¬¡æ¯å¦å®å¨ã ä¸»è¦ç¨äºè¿è¡æ¶ä¼åã è³å°åºè¯¥å£°æä¸ä¸ªéæ©ãå¦æä»»ä½ä¸ä¸ªé½æ²¡æåºç°ï¼é£ä¹ VOLATILE æ¯ç¼ºçåè®¾ã

IMMUTABLE è¡¨ç¤ºè¯¥å½æ°å¨ç»åºåæ ·çåæ°å¼æ¶æ»æ¯è¿åç¸åçç»æï¼ ä¹å°±æ¯è¯´ï¼å®ä¸åæ°æ®åºæ¥æ¾æèæ¯ä½¿ç¨é£äºå¹¶æ²¡æç´æ¥åºç°å¨å¶åæ°åè¡¨éé¢çä¿¡æ¯ã å¦æç»åºè¿ä¸ªéé¡¹ï¼é£ä¹ä»»ä½å¸¦çå¨é¨æ¯å¸¸éåæ°å¯¹è¯¥å½æ°çè°ç¨é½å°ç«å³æ¿æ¢ä¸ºè¯¥å½æ°çå¼ã

STABLE è¡¨ç¤ºå¨ä¸æ¬¡è¡¨æ«æéï¼å¯¹ç¸ååæ°å¼ï¼ è¯¥å½æ°å°ç¨³å®è¿åç¸åçå¼ï¼ä½æ¯å®çç»æå¯è½å¨ä¸å SQL è¯å¥ä¹é´ååã è¿ä¸ªéé¡¹å¯¹é£äºç»æåèµæ°æ®åºæ¥æ¾ï¼åæ°åéï¼æ¯å¦å½åæ¶åºï¼ï¼çç- çå½æ°æ¯å¾åéçã è¿è¦æ³¨æ current_timestamp æå½æ°æ¯ stable (ç¨³å®)çï¼å ä¸ºå®ä»¬çå¼å¨ä¸æ¬¡äºå¡ä¸ä¸ä¼ååã

VOLATILE è¡¨ç¤ºè¯¥å½æ°å¼çè³å¯ä»¥å¨ä¸æ¬¡è¡¨æ«æåæ¹åï¼ å æ- ¤ä¸ä¼åä»»ä½ä¼åãå¾å°æ°æ®åºå½æ°å¨è¿ä¸ªæ¦å¿µä¸æ¯æåçï¼ ä¸äºä¾åæ¯ random()ï¼currval()ï¼ timeofday()ãè¯·æ³¨æä»»ä½æå¯ä½ç¨çå½æ°é½å¿éåä¸ºæåç±»ï¼ å³ä½¿å¶ç»æç¸å½æè§å¾ä¹åºè¯¥è¿æ ·ï¼è¿æ ·æè½é¿åå®è¢«ä¼åï¼ä¸ä¸ªä¾å- å°±æ¯ setval()ã

CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT

STRICT

CALLED ON NULL INPUT ï¼ç¼ºçï¼è¡¨æè¯¥å½æ°å¨èªå·±çæäºåæ°æ¯ç©ºå¼çæ¶åè¿æ¯å¯ä»¥æç§æ- £å¸¸çæ¹å¼è°ç¨ã å©ä¸çäºææ¯å½æ°çä½èå¿é¡»è´è´£æ£æ¥ç©ºå¼ä»¥åç¸åºå°ååºååºã

RETURNS NULL ON NULL INPUT æ STRICT è¡¨æå¦æå®çä»»ä½åæ°æ¯ NULLï¼æ¤å½æ°æ»æ¯è¿å NULLã å¦æå£°æäºè¿ä¸ªåæ°ï¼åå¦æåå¨ NULL åæ°æ¶ä¸ä¼æ§è¡è¯¥å½æ°ï¼ èåªæ¯èªå¨åè®¾ä¸ä¸ª NULL ç»æã

[EXTERNAL] SECURITY INVOKER
[EXTERNAL] SECURITY DEFINER

SECURITY INVOKER è¡¨æè¯¥å½æ°å°å¸¦çè°ç¨å®çç¨æ·çæéæ§è¡ã è¿æ¯ç¼ºçãSECURITY DEFINER å£°æè¯¥å½æ°å°ä»¥åå»ºå®çç¨æ·çæéæ§è¡ã

å³é®å EXTERNAL çç®çæ¯å SQL å¼å®¹ï¼ ä½æ¯æä»¬å SQL ä¸åçæ¯ï¼è¿ä¸ªç¹æ§ä¸ä»ä»éç¨äºå¤é¨çå½æ°ï¼ æä»¥å®æ¯å¯éçã

definition

ä¸ä¸ªå®ä¹å½æ°çåä¸²ï¼å«ä¹åå³äºè¯è¨ãå®å¯ä»¥æ¯ä¸ä¸ªåé¨å½æ°ååï¼ ä¸ä¸ªæåæä¸ªç®æ æä»¶çè·¯å¾ï¼ä¸ä¸ª SQL æ¥è¯¢ï¼æèä¸ä¸ªç¨è¿ç¨è¯- è¨åçææ¬ã

obj_file, link_symbol

è¿ä¸ªå½¢å¼ç AS åå¥ç¨äºå¨å½æ°ç C æºæä»¶éçå½æ°ååå SQL å½æ°çåå- ä¸åçæ¶åå¯å¨æè£è½½ C è¯è¨å½æ°ã åä¸² obj_file æ¯åå«å¯å¨æè£è½½çå¯¹è±¡çæä»¶åï¼è link_symbol æ¯å½æ°çé¾æ¥ç¬¦å·ï¼ä¹å°±æ¯è¯¥å½æ°å¨ C æºæä»¶éçååã å¦æçç¥äºé¾æ¥ç¬¦å·ï¼é£ä¹å°±åè®¾å®åè¢«å®ä¹ç SQL å½æ°ååã

attribute

åå²éççå½æ°å¯éä¿¡æ¯ãä¸é¢çå±æ§å¯ä»¥å¨æ¤åºç°ï¼
isStrict

çæäº STRICT æè RETURNS NULL ON NULL INPUT

isCachable

isCachable æ¯ IMMUTABLE çè¿æ¶çç- æç©ï¼ä¸è¿åºäºåä¸å¼å®¹ï¼æä»¬ä»ç¶æ¥åå®ã

å±æ§åæ¯å¤§å°åæ å³çã

NOTES æ³¨æ

è¯·åé ‘‘User-Defined Functions’’ è·åæ´å¤å³äºä¹¦åå½æ°çä¿¡æ¯ã

æä»¬åè®¸ä½ å°å®æ´ç SQL ç±»åè¯æ³ç¨äºè¾å¥åæ°åè¿åå¼ã ä¸è¿ï¼æäºç±»åå£°æçç»èï¼æ¯å¦ï¼numeric ç±»åçç²¾åº¦åï¼æ¯ç±ä¸å±å½æ°å®ç°è´è´£çï¼ å¹¶ä¸ä¼è¢« CREATE FUNCTION å½ä»¤ææå°åæã ï¼ä¹å°±æ¯è¯´ï¼ä¸åè¢«è¯å«æå¼ºå¶ï¼ã

PostgreSQL åè®¸å½æ°éè½½ï¼ ä¹å°±æ¯è¯´ï¼åä¸ä¸ªå½æ°åå¯ä»¥ç¨äºå ä¸ªä¸åçå½æ°ï¼ åªè¦å®ä»¬çåæ°å¯ä»¥åºåå®ä»¬ãä¸è¿ï¼ææå½æ°ç C ååå¿é¡»ä¸åï¼ ä¹å°±æ¯è¯´ä½ å¿é¡»ç»äºéè½½ç C å½æ°ä¸åç C ååï¼æ¯å¦ï¼ä½¿ç¨åæ°ç±»åä½ä¸º C ååçä¸é¨åï¼ã

å¦æéå¤è°ç¨ CREATE FUNCTIONï¼å¹¶ä¸é½æååä¸ä¸ªç®æ æä»¶ï¼ é£ä¹è¯¥æä»¶åªè£è½½ä¸æ¬¡ãè¦å¸è½½åæ¢å¤è£è½½è¯¥æä»¶ï¼å¯è½æ¯å¨å¼åè¿ç¨ä¸- ï¼ï¼ä½ å¯ä»¥ä½¿ç¨ LOAD [load(7)] å½ä»¤ã

ä½¿ç¨ DROP FUNCTION å é¤ä¸ä¸ªç¨æ·å®ä¹å½æ°ã

å½æ°å®ä¹éé¢çä»»ä½åå¼å·æèåææ é½å¿é¡»ç¨ååä»½çæ¹å¼éé¸ã

è¦è½å®ä¹å½æ°ï¼ç¨æ·å¿é¡»å¯¹è¯¥è¯è¨æ USAGE æéã

EXAMPLES ä¾å

è¿éæ¯ä¸ä¸ªç®åçä¾åï¼ç¨äºå¸®å©ä½ å¼å§ææ¡è¿ä¸ªå½ä»¤ã æ´å¤ä¿¡æ¯åä¾åï¼åé ‘‘User-Defined Functions’’ã

CREATE FUNCTION add(integer, integer) RETURNS integer
AS ’select $1 + $2;’
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

COMPATIBILITY å¼å®¹æ§

å¨ SQL99 éçç¡®å®ä¹äºä¸ä¸ªCREATE FUNCTION PostgreSQL çåå®ç±»ä¼¼ä½æ¯ä¸å¼å®¹ã è¿ä¸ªå±æ§æ¯ä¸å¯ç§»æ¤çï¼å¯ä»¥ä½¿ç¨çä¸åè¯- è¨ä¹æ¯å¦æ¤ã

SEE ALSO åè§

ALTER FUNCTION [alter_function(7)], DROP FUNCTION [drop_function(7)], GRANT [grant(7)], LOAD [load(7)], REVOKE [revoke(7)], createlang(1)

è¯è

Postgresql ä¸æç½ç« ä½ä¼å¹³ <laser@pgsqldb.org>

è·

æ¬é¡µé¢ä¸æçç±ä¸æ man æåé¡µè®¡åæä¾ã
ä¸æ man æåé¡µè®¡åï¼https://github.com/man-pages-zh/manpages-zh

↑