php执行postgresql中的函数,PostgreSQL函数

PostgreSQL的函数也被称为存储过程，可执行操作，通常会作为一些查询和往返在一个单一的数据库内的函数。函数允许数据库重新使用其他应用程序可以直接与您的存储过程而不是一个中间层或复制代码。

yiibai.com

可以创建在所选择的语言，如SQL，PL/pgSQL，C，Python等功能 yiibai.com

语法

创建一个函数的基本语法如下：

www.yiibai.com

CREATE[OR REPLACE]FUNCTION function_name(arguments)RETURNS return_datatype AS $variable_name$

DECLARE

declaration;[...]BEGIN[...]RETURN{variable_name|value}END;LANGUAGE plpgsql; yiibai.com

Where,

www.yiibai.com

function-name specifies the name of the function. www.yiibai.com

[OR REPLACE] option allows modifying an existing function. www.yiibai.com

The function must contain a return statement.

yiibai.com

RETURN clause specifies that data type you are going to return from the function. Thereturn_datatype can be a base, composite, or domain type, or can reference the type of a table column. www.yiibai.com

function-body contains the executable part. yiibai.com

The AS keyword is used for creating a standalone function.

www.yiibai.com

plpgsql is the name of the language that the function is implemented in. Here we use this option for PostgreSQL, it Can be SQL, C, internal, or the name of a user-defined procedural language. For backward compatibility, the name can be enclosed by single quotes.

yiibai.com

语法

The following example illustrates creating and calling a standalone function. This function returns the total number of records in the COMPANY table. We will use the COMPANY table, which has following records:

www.yiibai.com

testdb# select * from COMPANY;

id | name | age | address | salary

----+-------+-----+-----------+--------

1 | Paul | 32 | California| 20000

2 | Allen | 25 | Texas | 15000

3 | Teddy | 23 | Norway | 20000

4 | Mark | 25 | Rich-Mond | 65000

5 | David | 27 | Texas | 85000

6 | Kim | 22 | South-Hall| 45000

7 | James | 24 | Houston | 10000

(7 rows)

yiibai.com

Function totalRecords() is as follows:

www.yiibai.com

CREATE OR REPLACE FUNCTION totalRecords()RETURNS integer AS $total$

declare

total integer;BEGINSELECT count(*)intototal FROM COMPANY;RETURN total;END;$total$ LANGUAGE plpgsql; www.yiibai.com

When the above query is executed the result would be:

www.yiibai.com

testdb# CREATE FUNCTION

www.yiibai.com

Now let's execute a call to this function and check the records in the COMPANY table

yiibai.com

testdb=#selecttotalRecords();

www.yiibai.com

When the above query is executed the result would be: www.yiibai.com

totalrecords

--------------

7

(1 row) www.yiibai.com