PostgreSQL7.0手册-程序员手册 -38. 扩展的 SQL: 函数
减小字体
增大字体| 第三十八章. 扩展的 SQL: 函数 内容 查询语言 （SQL）函数 过程语言函数 内部函数 编译 （C）语言函数 函数重载 正如我们想象的那样，定义新类型的一部分工作是定义描述（该类型）特征的函数．因此，我们可能只定义一个新函数而不定义新类型，反过来却不行．所以，我们先描述如何给 Postgres 增加新函数，然后再描述如何增加新类型． PostgresSQL 提供三种类型的函数： 查询语言函数（用 SQL 写的函数） 过程语言函数（用诸如 PLTCL 或 PLSQL 这样的语言写的函数） 编程语言函数（用类似 C 这样编译的编程语言写的函数） 每种函数都可以以一个基本类型或一个复合类型或是两者的某种组合作为参数．另外，每种函数都可以返回一个基本类型或一个复合类型值．定义 SQL 函数更容易些，所以我们将从这里开始．本章的例子还可以在 funcs.sql 和 funcs.c里找到． 查询语言（SQL）函数 SQL 函数执行一个任意 SQL 查询的列表，返回列表里最后一个查询的结果。SQL 函数通常返回集。如果它们的返回类型没有声明为 setof，那么将返回最后一条查询结果的任意元素。 跟在 AS 后面的 SQL 函数体应该是一个用空白字符分隔和用引号括起来的查询列表。要注意在查询里面的引号必须用前面带两个反斜杠的方法转意。 SQL 函数的参数在查询里可以用 $n 语法引用：$1 指第一个参数，$2 指第二个参数，以此类推。如果参数是复合类型，那么可以用点表示法（例如。"$1.emp"）访问参数里的字段或者激活函数。 例子 看看下面这个简单的 SQL 函数的例子，它将用于对一个银行帐号做扣款（借记消费 debit）动作： create function TP1 (int4, float8) returns int4 as 'update BANK set balance = BANK.balance - $2 where BANK.acctountno = $1 select(x = 1)' language 'sql'; 一个用户可以象下面这样用这个函数给帐户 17 扣款 $100.00： select (x = TP1( 17,100.0)); 下面的更有意思的例子接受一个类型为 EMP 的参数，并且检索多个结果： select function hobbies (EMP) returns set of HOBBIES（译注：开头的 select 应为 create） as 'select (HOBBIES.all) from HOBBIES where $1.name = HOBBIES.person' language 'sql'; 基本类型的 SQL 函数 最简单的 SQL 函数可能是不带参数，只是返回一个基本类型如 int4 的函数： CREATE FUNCTION one() RETURNS int4 AS 'SELECT 1 as RESULT' LANGUAGE 'sql'; SELECT one() AS answer; +-------+ answer +-------+ 1 +-------+ 注意我们给函数定义了目标列（名称为 RESULT），但是激活函数的查询语句的目标列覆盖了函数的目标列．因此，结果的标记是 answer 而不是 one 的． 定义以基本类型为参数的 SQL 函数几乎一样简单，注意我们在函数内如何用$1和$2使用参数： CREATE FUNCTION add_em(int4, int4) RETURNS int4 AS 'SELECT $1 + $2;' LANGUAGE 'sql'; SELECT add_em(1, 2) AS answer; +-------+ answer +-------+ 3 +-------+ 复合类型的 SQL 函数 当我们声明的函数用复合类型（如 EMP）做参数时，我们不仅要声明我们需要哪个参数（像上面我们使用$1和$2一样），而且要声明参数的字段．比如，把 double_salary 做为计算你薪水翻番之后的数值的函数： CREATE FUNCTION double_salary(EMP) RETURNS int4 AS 'SELECT $1.salary * 2 AS salary;' LANGUAGE 'sql'; SELECT name, double_salary(EMP) AS dream FROM EMP WHERE EMP.cubicle ~= '(2,1)'::point; +-----+-------+ name dream +-----+-------+ Sam 2400 +-----+-------+ 注意这里 $1.salary 语法的使用．在开始说明返回复合类型的函数之前，我们必须先介绍用于映射字段的函数表示法．我们可以用一个简单的方法解释这些：我们可以互换地使用 attribute(class) 和 class.attribute 两种表达方式： -- -- 这是与下面语句一样的： -- SELECT EMP.name AS youngster FROM EMP WHERE EMP.age < 30 -- SELECT name(EMP) AS youngster FROM EMP WHERE age(EMP) < 30; +----------+ youngster +----------+ Sam +----------+ 不过，我们呆会儿就会看到不总是这种情况．这种函数表示法在我们希望使用的函数返回单条记录时是很重要的．此时我们在函数里面一个字段一个字段地组装整条记录．下面是返回单条 EMP 记录的函数例子： CREATE FUNCTION new_emp() RETURNS EMP AS 'SELECT \'None\'::text AS name, 1000 AS salary, 25 AS age, \'(2,2)\'::point AS cubicle' LANGUAGE 'sql'; 在这个例子中我们把每个字段赋予了一个常量，当然我们可以用任何计算或表达式来代替这些常量．定义这样的函数可能需要一点点技巧．下面是一些比较重要的注意事项： 目标列表的顺序必须和你用 CREATE TABLE （或者你执行的 .* 查询里的）语句创建时的字段顺序一样． 你必须很仔细的进行类型转换 （使用 ::）．否则你将看到下面的错误信息： WARN::function declared to return type EMP does not retrieve (EMP.*) 当我们调用了一个返回整条记录的函数时，我们不能检索整条记录．我们要么是从该记录中映射出一个字段，要么是把整条记录传递给另外一个函数． SELECT name(new_emp()) AS nobody; +-------+ nobody +-------+ None +-------+ 通常我们必须把函数的返回值映射到字段里，使用这种函数语法是因为分析器在分析函数调用时，无法正确分析与函数相联的'点'的语法． SELECT new_emp().name AS nobody; WARN:parser: syntax error at or near "." 在 SQL 查询语言里的任何命令的集合都可以打包在一起，定义为函数．这些命令可以包含更新动作（例如，INSERT，UPDATE，和 DELETE），就象可以使用 SELECT 查询一样．不过，最后的命令必须是一条返回类型与所声明的函数返回类型的一致的 SELECT 语句。 CREATE FUNCTION clean_EMP () RETURNS int4 AS 'DELETE FROM EMP WHERE EMP.salary <= 0; SELECT 1 AS ignore_this' LANGUAGE 'sql'; SELECT clean_EMP(); +--+ x +--+ 1 +--+ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 过程语言函数 过程语言函数不是内建于 Postgres 里的。它们是通过可装载模块提供的。请参考相关的 PL 的文档获取关于语法和 AS 子句如何被 PL 句柄解释的细节。 在标准的 Postgres 版本里有两种可用的过程语言（PLTCL 和 PLSQL），并且可以定义其他语言。请参考 过程语言 获取详细信息。 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 内部函数 内部函数是那些用 C 写的函数，它们已经通过静态链接的方式链接进入Postgres 后端进程里面。AS 子句给出函数的 C 语言的名称，它不必与定义给 SQL 使用的名称相同。（出于向下兼容考虑，一个空的 AS 子句可以被接受，意味着 C 函数名与 SQL 函数名相同。）通常，所有在后端里出现的内部函数都在数据库初始化时定义为 SQL 函数，但是用户可以用 CREATE FUNCTION 为内部函数创建额外的别名。 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 编译（C）语言函数 用 C 写的函数可以编译成可动态装载的对象，然后用于实现用户定义的 SQL 函数。当用户定义的函数第一次被后端调用时，动态装载器把函数的目标码装载入内存，然后把这个函数与正在运行的Postgres 可执行文件链接起来。CREATE FUNCTION 的 SQL 语法用两种方法之一把 SQL 函数与 C 源函数链接起来。如果 SQL 函数与 C 源函数同名，使用语句的第一种形式。AS 子句里的字符串参数是包含已经编译好的可动态装载的对象的完整的路径名。如果 C 函数的名称与 SQL 函数的名称不同，则使用第二种形式。这种形式 AS 子句接受两个字符串参数，第一个是可动态装载目标文件的完整路径，第二个是动态装载器应搜索的链接符号。这个链接符号只是 C 源代码里的函数名。 注意：在第一次使用之后，一个动态装载的用户函数仍然停留在内存中，因而对该函数的更进一步的调用只是简单的符号表查找。 声明目标文件的参数（AS 子句里的字符串）应该是该函数目标文件的完整路径，并用引号括起来。如在 AS 子句里使用了链接符号，链接符号也应该用单引号括起来，并且就应该是 C 源代码里函数的名称。在 Unix 系统里，命令 nm 会打印出一个可动态装载的对象里的所有链接符号。（Postgres 不会自动编译一个函数；该函数必须在使用 CREATE FUNCTION 命令之前编译。参阅下文获取额外信息。） 基本类型的 C 语言函数 下表列出了被装载入 Postgres 的 C 函数里需要的当作参数的 C 类型。"定义在" 列给出了等效的 C 类型定义的实际的头文件（在 .../src/backend/ 目录里）。如果你包含了utils/builtins.h，这些文件将被自动包括。 表 38-1. 内建的 Postgres 类型等效的 C 类型 内建类型 C 类型 定义在 abstime AbsoluteTime utils/nabstime.h bool bool include/c.h box (BOX *) utils/geo-decls.h bytea (bytea *) include/postgres.h char char N/A cid CID include/postgres.h datetime (DateTime *) include/c.h or include/postgres.h int2 int2 include/postgres.h int2vector (int2vector *) include/postgres.h int4 int4 include/postgres.h float4 float32 or (float4 *) include/c.h or include/postgres.h float8 float64 or (float8 *) include/c.h or include/postgres.h lseg (LSEG *) include/geo-decls.h name (Name) include/postgres.h oid oid include/postgres.h oidvector (oidvector *) include/postgres.h path (PATH *) utils/geo-decls.h point (POINT *) utils/geo-decls.h regproc regproc or REGPROC include/postgres.h reltime RelativeTime utils/nabstime.h text (text *) include/postgres.h tid ItemPointer storage/itemptr.h timespan (TimeSpan *) include/c.h or include/postgres.h tinterval TimeInterval utils/nabstime.h uint2 uint16 include/c.h uint4 uint32 include/c.h xid (XID *) include/postgres.h Postgres 内部把基本类型当作"一片内存"看待．定义在某种类型上的用户定义函数实际上定义了 Postgres对（该数据类型）可能的操作．也就是说，Postgres 只是从磁盘读取和存储该数据类型，而使用你定义的函数来输入，处理和输出数据．基本类型可以有下面三种内部形态（格式）之一： 传递数值，定长（pass by value, fixed-length） 传递引用，定长（pass by reference, fixed-length） 传递引用，变长（pass by reference, variable-length） 传递数值的类型的长度只能是1，2 或 4 字节．（即便你的计算机支持其他长度的传值类型也是这样）．Postgres 本身的传值类型只能是整数．你要仔细定义你的类型，确保它们在任何体系平台上都是相同尺寸（字节）．例如，long 型是一个危险的类型因为在一些机器上它是 4 字节而在另外一些机器上是 8 字节，而 int 型在大多数 Unix 机器上都是4字节的（尽管不是在多数个人微机上）．在一个 Unix 机器上的 int4 合理的实现可能是： /* 4-byte integer, passed by value */ typedef int int4; 另外，任何尺寸的定长类型都可以是传递引用型．例如，下面是一个 Postgres 类型的实现： /* 16-byte structure, passed by reference */ typedef struct { double x, y; } Point; 只能使用指向这些类型的指针来在 Postgres 函数里输入和输出．最后，所有变长类型同样也只能通过传递引用的方法来传递．所有变长类型必须以一个4字节长的长度域开始，并且所有存储在该类型的数据必须放在紧接着长度域的存储空间里．长度域是结构的全长（也就是说，包括长度域本身的长度）．我们可以用下面方法定义一个 text 类型： typedef struct { int4 length; char data[1]; } text; 显然，上面的数据域不够存储任何可能的字串 －－ 在 C 中定义这么个结构是不可能的．当处理变长类型时，我们必须仔细分配正确的存储器数量并初始化长度域．例如，如果我们想在一个 text 结构里存储 40 字节，我们可能会使用象下面的代码片段： #include "postgres.h" ... char buffer[40]; /* our source data */ ... text *destination = (text *) palloc(VARHDRSZ + 40); destination->length = VARHDRSZ + 40; memmove(destination->data, buffer, 40); ... 既然我们已经讨论了基本类型所有的可能结构，我们便可以用实际的函数举一些例子．假设 funcs.c 象下面一样： #include #include "postgres.h" /* By Value */ int add_one(int arg) { return(arg + 1); } /* By Reference, Fixed Length */ Point * makepoint(Point *pointx, Point *pointy ) { Point *new_point = (Point *) palloc(sizeof(Point)); new_point->x = pointx->x; new_point->y = pointy->y; return new_point; } /* By Reference, Variable Length */ text * copytext(text *t) { /* * VARSIZE is the total size of the struct in bytes. */ text *new_t = (text *) palloc(VARSIZE(t)); memset(new_t, 0, VARSIZE(t)); VARSIZE(new_t) = VARSIZE(t); /* * VARDATA is a pointer to the data region of the struct. */ memcpy((void *) VARDATA(new_t), /* destination */ (void *) VARDATA(t), /* source */ VARSIZE(t)-VARHDRSZ); /* how many bytes */ return(new_t); } text * concat_text(text *arg1, text *arg2) { int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ; text *new_text = (text *) palloc(new_text_size); memset((void *) new_text, 0, new_text_size); VARSIZE(new_text) = new_text_size; strncpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1)-VARHDRSZ); strncat(VARDATA(new_text), VARDATA(arg2), VARSIZE(arg2)-VARHDRSZ); return (new_text); } 在 OSF/1 （平台上）我们要敲入： CREATE FUNCTION add_one(int4) RETURNS int4 AS 'PGROOT/tutorial/funcs.so' LANGUAGE 'c'; CREATE FUNCTION makepoint(point, point) RETURNS point AS 'PGROOT/tutorial/funcs.so' LANGUAGE 'c'; CREATE FUNCTION concat_text(text, text) RETURNS text AS 'PGROOT/tutorial/funcs.so' LANGUAGE 'c'; CREATE FUNCTION copytext(text) RETURNS text AS 'PGROOT/tutorial/funcs.so' LANGUAGE 'c'; On other systems, we might have to make the filename end in .sl (to indicate that it's a shared library). 复合类型的 C 语言函数 复合类型不象 C 结构那样有固定的布局．复合类型的记录可能包含空（null）域．另外，一个属于继承层次一部分的复合类型可能和同一继承范畴的其他成员有不同的域/字段．因此，Postgres 提供一个过程接口用于从 C 里面访问复合类型．在 Postgres 处理一个记录集时，每条记录都将作为一个类型 TUPLE 的不透明（opaque）的结构被传递给你的函数．假设我们为下面查询写一个函数 * SELECT name, c_overpaid(EMP, 1500) AS overpaid FROM EMP WHERE name = 'Bill' or name = 'Sam'; 在上面的查询里，我们可以这样定义 c_overpaid ： #include "postgres.h" #include "executor/executor.h" /* for GetAttributeByName() */ bool c_overpaid(TupleTableSlot *t, /* the current instance of EMP */ int4 limit) { bool isnull = false; int4 salary; salary = (int4) GetAttributeByName(t, "salary", &isnull); if (isnull) return (false); return(salary > limit); } GetAttributeByName 是 Postgres 系统函数，用来返回当前记录的字段值．它有三个参数：类型为 TUPLE 的传入函数的参数，需要的字段名称，以及一个用以确定字段是否为空（null）的返回参数指针．GetAttributeByName 会把数据正确的对齐，这样你就可以把返回值转换成合适的类型．例如，如果你有一个字段的名称就是类型名，调用 GetAttributeByName 就会看起来象： char *str; ... str = (char *) GetAttributeByName(t, "name", &isnull) 下面的查询让 Postgres 知道 c_overpaid 函数： * CREATE FUNCTION c_overpaid(EMP, int4) RETURNS bool AS 'PGROOT/tutorial/obj/funcs.so' LANGUAGE 'c'; 当然还有其他方法在 C 函数里构造新的记录或修改现有记录，这些方法都太复杂，不适合在本手册里讨论． 书写代码 我们现在转到了书写编程语言函数的更难的阶段．要注意：本手册此章的内容不会让你成为程序员．在你尝试用 C 书写用于 Postgres 的函数之前，你必须对 C 有很深的了解（包括对指针的使用和 malloc 存储器管理）．虽然可以用 C 以外的其他语言如 FORTRAN 和 Pascal 书写用于 Postgres 的共享函数，但通常很麻烦（虽然是完全可能的），因为其他语言并不遵循和 C 一样的调用习惯．也就是说，其他语言与 C 的传参和返回值的方式不一样．因此我们假设你的编程语言函数是用 C 写的． 以基本类型为参数的 C 函数可以用直接的风格书写。内建的 Postgres 类型的 C 等效物可以通过把文件 PGROOT/src/backend/utils/builtins.h 做为头文件包含到 C 文件里访问。可以向 C 文件的头部增加 #include 这一行实现这些。 制作 C 函数的基本规则如下： 大多数用于 Postgres 的头文件（include）应该已经安装在PGROOT/include（参见图 2）里．你应该总是将 -I$PGROOT/include 包括在你的 cc 命令行里．有时候，你可能发现你需要的头文件在服务器源代码里（比如，你需要的文件被忘了装在 include 路径里）．在这种情况下，你可能要增加象下面一条或几条 -I$PGROOT/src/backend -I$PGROOT/src/backend/include -I$PGROOT/src/backend/port/ -I$PGROOT/src/backend/obj （这里 当分配存储器时，用 Postgres 的函数 palloc 和 pfree 取代相应的 C 库函数 malloc 和 free．用 palloc 分配的存储器在事务结束时会自动释放，避免了内存泄露． 记得用 memset 或 bzero 对你的结构清零．有些函数（像 hash （散列）访问方法，hash （散列）联合和排序算法）计算包含在你的结构里的裸位（row bit）．即使你初始化了你的结构的所有域，仍然有可能有几个对齐字节（结构中的洞）含有垃圾值． 大多数的 Postgres 内部类型定义在 postgres.h，所以始终包含该文件是一个好习惯．包含了 postgres.h 将自动包含 elog.h 和 palloc.h． 把你的目标码编译和装载成可以动态装入 Postgres 的库文件总是需要一些特殊的标记。参阅 链接动态链接库 获取如何在你的平台上做这件事的详细说明。 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 函数重载 如果函数的参数不同，可以定义为同名函数。换句话说，函数名可以重载。函数也可以与一个字段/属性同名。这时，一个复合类型的函数和一个复合类型的字段/属性会有冲突，这种情况下总是使用字段/属性。 名字空间冲突 对于 Postgres v7.0，SQL CREATE FUNCTION 命令的 AS 子句把 SQL 函数名和 C 源代码的函数名脱钩。目前这是实现函数重载的比较好的技巧。 v7.0 以前 对于用 C 写的函数，在 CREATE FUNCTION 里定义的 SQL 名称必须和 C 代码里的实际函数名称完全一样（因此它必须是一个合法的 C 函数名）。 这样的限制有个小小的暗示：尽管大多数的操作系统的动态链接过程允许你装载任意数目的包含冲突（同名的）的函数名的共享库，实际上它们可能用某种有趣的方式修补这样的装载。例如，如果你定义了一个与内建于 Postgres 的函数同名的动态装载的函数，DEC OSF/1 的动态装载器会令 Postgres 调用它自己内部的函数而不是令 Postgres 调用你的函数。因此，如果你希望你的函数用于不同的体系，我们建议你不要重载 C 函数名。 我们可以用一个很聪明的技巧绕开上面提到的问题。因为重载 SQL 函数没有问题，所以你可以定义一套不同名的 C 函数，然后定义一套同名的 SQL 函数封装，这些 SQL 函数封装接收合适的参数类型并且调用对应的 C 函数。 另一个解决方法是避免使用动态装载，而是把你的函数和后端进行静态链接，并且把他们定义为 INTERNAL 函数。这样，函数必须有不同的 C 名称，但是可以定义为相同的 SQL 名称（当然它们的参数类型不同）。这个方法避免了 SQL 封装函数的过荷，付出的代价是制作一个客户化的后端可执行文件。（这个选项只有在 v6.5 和以后的版本中有，因为以前的版本要求内部函数与 SQL 函数有与 C 代码里的一样的名称。） -------------------------------------------------------------------------------- |
请扫描二维码添加站长微信