PostgreSQL䔇庘昄幘赆䓄婺庻嗘誺䘋埇欓臯淉嘩锔婩嚔嘩婺婔底昖臵启冔誫婘婔婻剘婔䔇昄扞康喙䔇庘昄㔗庘昄噕螩昄扞康麉桄嘪䫘噽傡庫䫘䘋废埇傖䕘毖婯旘䔇庻嗘誺䘋蔯婉滇婔婻婺閘北潡崉彽傼乕㔗

埇傖录傺婘欔锬拷䔇臺蘔套SQLPL/pgSQLCPython京媘脘 gitbook.net

臺濘

录傺婔婻庘昄䔇嘺橸臺濘套婋

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; gitbook.net 

Where,

• function-name specifies the name of the function. www.gitbook.net

• [OR REPLACE] option allows modifying an existing function. www.gitbook.net

• The function must contain a return statement.

  gitbook.net

• 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.gitbook.net

• function-body contains the executable part. gitbook.net

• The AS keyword is used for creating a standalone function.

  www.gitbook.net

• 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.

  gitbook.net

臺濘

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:

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)
 
gitbook.net
 

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.gitbook.net 

When the above query is executed the result would be:

testdb# CREATE FUNCTION
 
www.gitbook.net
 

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

gitbook.net

testdb=# select totalRecords(); 
www.gitbook.net
 

When the above query is executed the result would be: www.gitbook.net

 totalrecords
--------------
            7
(1 row) www.gitbook.net