postgresql函数

发布时间：2020-12-13 17:05:57 所属栏目：百科来源：网络整理

导读：来自：http://www.yiibai.com/html/postgresql/2013/080784.html PostgreSQL的函数也被称为存储过程，可执行操作，通常会作为一些查询和往返在一个单一的数据库内的函数。函数允许数据库重新使用其他应用程序可以直接与您的存储过程而不是一个中间层或复制代

来自：http://www.yiibai.com/html/postgresql/2013/080784.html

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

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

语法

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

CREATE [OR REPLACE] FUNCTION function_name (arguments) 
RETURNS return_datatype AS $variable_name$
  DECLARE
    declaration;
    [...]
  BEGIN
    < function_body >
    [...]
    RETURN { variable_name | value }
  END; LANGUAGE plpgsql;

Where,

function-namespecifies the name of the function. www.yiibai.com
[OR REPLACE] option allows modifying an existing function.
The function must contain areturnstatement.
RETURNclause specifies that data type you are going to return from the function. Thereturn_datatypecan be a base,composite,or domain type,or can reference the type of a table column.
function-bodycontains the executable part.
The AS keyword is used for creating a standalone function.
plpgsqlis 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.

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 theCOMPANYtable,which has following records:

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)

Function totalRecords() is as follows:

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
	total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql; www.yiibai.com

When the above query is executed the result would be:

testdb# CREATE FUNCTION

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

testdb=# select totalRecords();

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

 totalrecords
--------------
            7
(1 row) www

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!