{"Entry":{"collection":"language","key":"plpgsql","name":"plpgsql","aliases":[],"metadata":{"aliases":[],"category":"PL/pgSQL","content_hash":"8388f49bcb8ba36944d1bc7305f823491e9bfae0287923008f0e9648c2cdf76b","imported_at":"2026-09-30T00:40:44.348254+08:00","name":"plpgsql","name_zh":"","slug":"plpgsql","summary":"PL/pgSQL procedural language"}},"Definition":{"Collection":"language","Key":"plpgsql","SourceDatabase":"center","Version":"18","SourceTable":"procedural_language","SourceKey":"plpgsql","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","Facts":{"aliases":[],"attributes":{"comment":"PL/pgSQL procedural language","default_version":"1.0","family":"PL/pgSQL","handler":"plpgsql_call_handler","inline_handler":"plpgsql_inline_handler","kind":"Bundled procedural-language variant","library":"$libdir/plpgsql","module_pathname":"$libdir/plpgsql","trusted":"t","validator":"plpgsql_validator"},"comparison_data":{"default_version":"1.0","family":"PL/pgSQL","handler":"plpgsql_call_handler","inline_handler":"plpgsql_inline_handler","kind":"Bundled procedural-language variant","library":"$libdir/plpgsql","trusted":"t","validator":"plpgsql_validator"},"comparison_hash":"e85051f39a242ce658cedd87ac97437817f0f1b2baf992d8d37c9d29df8b558b","description":["PL/pgSQL procedural language"],"facts":[{"label":"Kind","value":"Bundled procedural-language variant"},{"label":"Family","value":"PL/pgSQL"},{"label":"Trusted","value":"t"},{"label":"Handler","value":"plpgsql_call_handler"},{"label":"Inline handler","value":"plpgsql_inline_handler"},{"label":"Validator","value":"plpgsql_validator"},{"label":"Library","value":"$libdir/plpgsql"},{"label":"Default version","value":"1.0"},{"label":"Module pathname","value":"$libdir/plpgsql"},{"label":"Comment","value":"PL/pgSQL procedural language"}],"manual_html":"\u003cdiv class=\"sect1\" id=\"PLPGSQL-OVERVIEW\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2 class=\"title\"\u003e41.1. Overview \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003e\u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e is a loadable procedural language for the \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e database system. The design goals of \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e were to create a loadable procedural language that\u003c/p\u003e\n\u003cdiv class=\"itemizedlist\"\u003e\n\u003cul class=\"itemizedlist\"\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003ecan be used to create functions, procedures, and triggers,\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eadds control structures to the SQL language,\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003ecan perform complex computations,\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003einherits all user-defined types, functions, procedures, and operators,\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003ecan be defined to be trusted by the server,\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eis easy to use.\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eFunctions created with \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e can be used anywhere that built-in functions could be used. For example, it is possible to create complex conditional computation functions and later use them to define operators or use them in index expressions.\u003c/p\u003e\n\u003cp\u003eIn \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 9.0 and later, \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e is installed by default. However it is still a loadable module, so especially security-conscious administrators could choose to remove it.\u003c/p\u003e\n\u003cdiv class=\"sect2\" id=\"PLPGSQL-ADVANTAGES\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e41.1.1. Advantages of Using \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eSQL is the language \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e and most other relational databases use as query language. It's portable and easy to learn. But every SQL statement must be executed individually by the database server.\u003c/p\u003e\n\u003cp\u003eThat means that your client application must send each query to the database server, wait for it to be processed, receive and process the results, do some computation, then send further queries to the server. All this incurs interprocess communication and will also incur network overhead if your client is on a different machine than the database server.\u003c/p\u003e\n\u003cp\u003eWith \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e you can group a block of computation and a series of queries \u003cspan class=\"emphasis\"\u003e\u003cem\u003einside\u003c/em\u003e\u003c/span\u003e the database server, thus having the power of a procedural language and the ease of use of SQL, but with considerable savings of client/server communication overhead.\u003c/p\u003e\n\u003cdiv class=\"itemizedlist\"\u003e\n\u003cul class=\"itemizedlist\"\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eExtra round trips between client and server are eliminated\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eIntermediate results that the client does not need do not have to be marshaled or transferred between server and client\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eMultiple rounds of query parsing can be avoided\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eThis can result in a considerable performance increase as compared to an application that does not use stored functions.\u003c/p\u003e\n\u003cp\u003eAlso, with \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e you can use all the data types, operators and functions of SQL.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"PLPGSQL-ARGS-RESULTS\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e41.1.2. Supported Argument and Result Data Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eFunctions written in \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e can accept as arguments any scalar or array data type supported by the server, and they can return a result of any of these types. They can also accept or return any composite type (row type) specified by name. It is also possible to declare a \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e function as accepting \u003ccode class=\"type\"\u003erecord\u003c/code\u003e, which means that any composite type will do as input, or as returning \u003ccode class=\"type\"\u003erecord\u003c/code\u003e, which means that the result is a row type whose columns are determined by specification in the calling query, as discussed in \u003ca class=\"xref\" href=\"/docs/18/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS\" title=\"7.2.1.4. Table Functions\"\u003eSection 7.2.1.4\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003e\u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e functions can be declared to accept a variable number of arguments by using the \u003ccode class=\"literal\"\u003eVARIADIC\u003c/code\u003e marker. This works exactly the same way as for SQL functions, as discussed in \u003ca class=\"xref\" href=\"/docs/18/xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS\" title=\"36.5.6. SQL Functions with Variable Numbers of Arguments\"\u003eSection 36.5.6\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003e\u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e functions can also be declared to accept and return the polymorphic types described in \u003ca class=\"xref\" href=\"/docs/18/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC\" title=\"36.2.5. Polymorphic Types\"\u003eSection 36.2.5\u003c/a\u003e, thus allowing the actual data types handled by the function to vary from call to call. Examples appear in \u003ca class=\"xref\" href=\"/docs/18/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS\" title=\"41.3.1. Declaring Function Parameters\"\u003eSection 41.3.1\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003e\u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e functions can also be declared to return a \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eset\u003c/span\u003e”\u003c/span\u003e (or table) of any data type that can be returned as a single instance. Such a function generates its output by executing \u003ccode class=\"command\"\u003eRETURN NEXT\u003c/code\u003e for each desired element of the result set, or by using \u003ccode class=\"command\"\u003eRETURN QUERY\u003c/code\u003e to output the result of evaluating a query.\u003c/p\u003e\n\u003cp\u003eFinally, a \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e function can be declared to return \u003ccode class=\"type\"\u003evoid\u003c/code\u003e if it has no useful return value. (Alternatively, it could be written as a procedure in that case.)\u003c/p\u003e\n\u003cp\u003e\u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e functions can also be declared with output parameters in place of an explicit specification of the return type. This does not add any fundamental capability to the language, but it is often convenient, especially for returning multiple values. The \u003ccode class=\"literal\"\u003eRETURNS TABLE\u003c/code\u003e notation can also be used in place of \u003ccode class=\"literal\"\u003eRETURNS SETOF\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eSpecific examples appear in \u003ca class=\"xref\" href=\"/docs/18/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS\" title=\"41.3.1. Declaring Function Parameters\"\u003eSection 41.3.1\u003c/a\u003e and \u003ca class=\"xref\" href=\"/docs/18/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING\" title=\"41.6.1. Returning from a Function\"\u003eSection 41.6.1\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e","manual_path":"/docs/18/plpgsql-overview.html","related":[{"label":"plpgsql-control-structures","url":"/docs/18/plpgsql-control-structures.html"},{"label":"plpgsql-cursors","url":"/docs/18/plpgsql-cursors.html"},{"label":"plpgsql-declarations","url":"/docs/18/plpgsql-declarations.html"},{"label":"plpgsql-development-tips","url":"/docs/18/plpgsql-development-tips.html"},{"label":"plpgsql-errors-and-messages","url":"/docs/18/plpgsql-errors-and-messages.html"},{"label":"plpgsql-expressions","url":"/docs/18/plpgsql-expressions.html"},{"label":"plpgsql-implementation","url":"/docs/18/plpgsql-implementation.html"},{"label":"plpgsql-overview","url":"/docs/18/plpgsql-overview.html"},{"label":"plpgsql-porting","url":"/docs/18/plpgsql-porting.html"},{"label":"plpgsql-statements","url":"/docs/18/plpgsql-statements.html"},{"label":"plpgsql-structure","url":"/docs/18/plpgsql-structure.html"},{"label":"plpgsql-transactions","url":"/docs/18/plpgsql-transactions.html"},{"label":"plpgsql-trigger","url":"/docs/18/plpgsql-trigger.html"}],"release":{"catalog_fingerprint":"65c93d6048ef30e61023a84f9680fa6a92b1c383b7eb226741170077eb078502","channel":"stable","label":"18.6","major":"18","ref":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"},"sections":[{"code":"/* src/pl/plpgsql/src/plpgsql--1.0.sql */\n\nCREATE FUNCTION plpgsql_call_handler() RETURNS language_handler\n  LANGUAGE c AS 'MODULE_PATHNAME';\n\nCREATE FUNCTION plpgsql_inline_handler(internal) RETURNS void\n  STRICT LANGUAGE c AS 'MODULE_PATHNAME';\n\nCREATE FUNCTION plpgsql_validator(oid) RETURNS void\n  STRICT LANGUAGE c AS 'MODULE_PATHNAME';\n\nCREATE TRUSTED LANGUAGE plpgsql\n  HANDLER plpgsql_call_handler\n  INLINE plpgsql_inline_handler\n  VALIDATOR plpgsql_validator;\n\n-- The language object, but not the functions, can be owned by a non-superuser.\nALTER LANGUAGE plpgsql OWNER TO @extowner@;\n\nCOMMENT ON LANGUAGE plpgsql IS 'PL/pgSQL procedural language';","paragraphs":["This extension is shipped with the source. Its presence does not establish that the language or external runtime is installed."],"title":"Installation definition from the matching source archive"}],"signature":"","source_files":{"src/pl/plpgsql/src/plpgsql--1.0.sql":"f4e7e05438808ac0da0b3801397d16e36102969af7e2ffc982def5b7524fb557","src/pl/plpgsql/src/plpgsql.control":"9457c3f51d6bdfab93f13080d177795a9274c025e378fb613218342e3a3c7b45"},"sources":[{"label":"Matching PostgreSQL source archive","sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"label":"PostgreSQL 18 English manual","path":"plpgsql-overview.html","sha256":"7f93dfdf27c74b24d0bd355e845c7a6ff142aa9479bdcd0f203e8d37f36a3dfc","url":"/docs/18/plpgsql-overview.html"}],"tables":[]},"ManualEvidence":{"manual_path":"/docs/18/plpgsql-overview.html","release":{"catalog_fingerprint":"65c93d6048ef30e61023a84f9680fa6a92b1c383b7eb226741170077eb078502","channel":"stable","label":"18.6","major":"18","ref":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"},"sources":[{"label":"Matching PostgreSQL source archive","sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"label":"PostgreSQL 18 English manual","path":"plpgsql-overview.html","sha256":"7f93dfdf27c74b24d0bd355e845c7a6ff142aa9479bdcd0f203e8d37f36a3dfc","url":"/docs/18/plpgsql-overview.html"}]},"MeasuredEvidence":{}},"Text":{"Collection":"language","Key":"plpgsql","SourceDatabase":"center","Version":"18","Locale":"en","Title":"plpgsql","Summary":"PL/pgSQL procedural language","BodyHTML":"\u003cdiv id=\"PLPGSQL-OVERVIEW\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2\u003e41.1. Overview \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003e\u003cspan\u003ePL/pgSQL\u003c/span\u003e is a loadable procedural language for the \u003cspan\u003ePostgreSQL\u003c/span\u003e database system. The design goals of \u003cspan\u003ePL/pgSQL\u003c/span\u003e were to create a loadable procedural language that\u003c/p\u003e\n\u003cdiv\u003e\n\u003cul\u003e\n\u003cli\u003e\n\u003cp\u003ecan be used to create functions, procedures, and triggers,\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003eadds control structures to the SQL language,\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003ecan perform complex computations,\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003einherits all user-defined types, functions, procedures, and operators,\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003ecan be defined to be trusted by the server,\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003eis easy to use.\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eFunctions created with \u003cspan\u003ePL/pgSQL\u003c/span\u003e can be used anywhere that built-in functions could be used. For example, it is possible to create complex conditional computation functions and later use them to define operators or use them in index expressions.\u003c/p\u003e\n\u003cp\u003eIn \u003cspan\u003ePostgreSQL\u003c/span\u003e 9.0 and later, \u003cspan\u003ePL/pgSQL\u003c/span\u003e is installed by default. However it is still a loadable module, so especially security-conscious administrators could choose to remove it.\u003c/p\u003e\n\u003cdiv id=\"PLPGSQL-ADVANTAGES\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e41.1.1. Advantages of Using \u003cspan\u003ePL/pgSQL\u003c/span\u003e \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eSQL is the language \u003cspan\u003ePostgreSQL\u003c/span\u003e and most other relational databases use as query language. It\u0026#39;s portable and easy to learn. But every SQL statement must be executed individually by the database server.\u003c/p\u003e\n\u003cp\u003eThat means that your client application must send each query to the database server, wait for it to be processed, receive and process the results, do some computation, then send further queries to the server. All this incurs interprocess communication and will also incur network overhead if your client is on a different machine than the database server.\u003c/p\u003e\n\u003cp\u003eWith \u003cspan\u003ePL/pgSQL\u003c/span\u003e you can group a block of computation and a series of queries \u003cspan\u003e\u003cem\u003einside\u003c/em\u003e\u003c/span\u003e the database server, thus having the power of a procedural language and the ease of use of SQL, but with considerable savings of client/server communication overhead.\u003c/p\u003e\n\u003cdiv\u003e\n\u003cul\u003e\n\u003cli\u003e\n\u003cp\u003eExtra round trips between client and server are eliminated\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003eIntermediate results that the client does not need do not have to be marshaled or transferred between server and client\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003eMultiple rounds of query parsing can be avoided\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eThis can result in a considerable performance increase as compared to an application that does not use stored functions.\u003c/p\u003e\n\u003cp\u003eAlso, with \u003cspan\u003ePL/pgSQL\u003c/span\u003e you can use all the data types, operators and functions of SQL.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv id=\"PLPGSQL-ARGS-RESULTS\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e41.1.2. Supported Argument and Result Data Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eFunctions written in \u003cspan\u003ePL/pgSQL\u003c/span\u003e can accept as arguments any scalar or array data type supported by the server, and they can return a result of any of these types. They can also accept or return any composite type (row type) specified by name. It is also possible to declare a \u003cspan\u003ePL/pgSQL\u003c/span\u003e function as accepting \u003ccode\u003erecord\u003c/code\u003e, which means that any composite type will do as input, or as returning \u003ccode\u003erecord\u003c/code\u003e, which means that the result is a row type whose columns are determined by specification in the calling query, as discussed in \u003ca href=\"/docs/18/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS\" rel=\"nofollow\"\u003eSection 7.2.1.4\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003e\u003cspan\u003ePL/pgSQL\u003c/span\u003e functions can be declared to accept a variable number of arguments by using the \u003ccode\u003eVARIADIC\u003c/code\u003e marker. This works exactly the same way as for SQL functions, as discussed in \u003ca href=\"/docs/18/xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS\" rel=\"nofollow\"\u003eSection 36.5.6\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003e\u003cspan\u003ePL/pgSQL\u003c/span\u003e functions can also be declared to accept and return the polymorphic types described in \u003ca href=\"/docs/18/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC\" rel=\"nofollow\"\u003eSection 36.2.5\u003c/a\u003e, thus allowing the actual data types handled by the function to vary from call to call. Examples appear in \u003ca href=\"/docs/18/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS\" rel=\"nofollow\"\u003eSection 41.3.1\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003e\u003cspan\u003ePL/pgSQL\u003c/span\u003e functions can also be declared to return a \u003cspan\u003e“\u003cspan\u003eset\u003c/span\u003e”\u003c/span\u003e (or table) of any data type that can be returned as a single instance. Such a function generates its output by executing \u003ccode\u003eRETURN NEXT\u003c/code\u003e for each desired element of the result set, or by using \u003ccode\u003eRETURN QUERY\u003c/code\u003e to output the result of evaluating a query.\u003c/p\u003e\n\u003cp\u003eFinally, a \u003cspan\u003ePL/pgSQL\u003c/span\u003e function can be declared to return \u003ccode\u003evoid\u003c/code\u003e if it has no useful return value. (Alternatively, it could be written as a procedure in that case.)\u003c/p\u003e\n\u003cp\u003e\u003cspan\u003ePL/pgSQL\u003c/span\u003e functions can also be declared with output parameters in place of an explicit specification of the return type. This does not add any fundamental capability to the language, but it is often convenient, especially for returning multiple values. The \u003ccode\u003eRETURNS TABLE\u003c/code\u003e notation can also be used in place of \u003ccode\u003eRETURNS SETOF\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eSpecific examples appear in \u003ca href=\"/docs/18/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS\" rel=\"nofollow\"\u003eSection 41.3.1\u003c/a\u003e and \u003ca href=\"/docs/18/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING\" rel=\"nofollow\"\u003eSection 41.6.1\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","ContentHash":"447891d1e917851248e9a0c91e960c47a334d6efa5a6bd58e55cf65eb1efa251","Payload":{"description":["PL/pgSQL procedural language"],"manual_html":"\u003cdiv class=\"sect1\" id=\"PLPGSQL-OVERVIEW\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2 class=\"title\"\u003e41.1. Overview \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\n\u003cp\u003e\u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e is a loadable procedural language for the \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e database system. The design goals of \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e were to create a loadable procedural language that\u003c/p\u003e\n\u003cdiv class=\"itemizedlist\"\u003e\n\u003cul class=\"itemizedlist\"\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003ecan be used to create functions, procedures, and triggers,\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eadds control structures to the SQL language,\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003ecan perform complex computations,\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003einherits all user-defined types, functions, procedures, and operators,\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003ecan be defined to be trusted by the server,\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eis easy to use.\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eFunctions created with \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e can be used anywhere that built-in functions could be used. For example, it is possible to create complex conditional computation functions and later use them to define operators or use them in index expressions.\u003c/p\u003e\n\u003cp\u003eIn \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 9.0 and later, \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e is installed by default. However it is still a loadable module, so especially security-conscious administrators could choose to remove it.\u003c/p\u003e\n\u003cdiv class=\"sect2\" id=\"PLPGSQL-ADVANTAGES\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e41.1.1. Advantages of Using \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eSQL is the language \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e and most other relational databases use as query language. It's portable and easy to learn. But every SQL statement must be executed individually by the database server.\u003c/p\u003e\n\u003cp\u003eThat means that your client application must send each query to the database server, wait for it to be processed, receive and process the results, do some computation, then send further queries to the server. All this incurs interprocess communication and will also incur network overhead if your client is on a different machine than the database server.\u003c/p\u003e\n\u003cp\u003eWith \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e you can group a block of computation and a series of queries \u003cspan class=\"emphasis\"\u003e\u003cem\u003einside\u003c/em\u003e\u003c/span\u003e the database server, thus having the power of a procedural language and the ease of use of SQL, but with considerable savings of client/server communication overhead.\u003c/p\u003e\n\u003cdiv class=\"itemizedlist\"\u003e\n\u003cul class=\"itemizedlist\"\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eExtra round trips between client and server are eliminated\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eIntermediate results that the client does not need do not have to be marshaled or transferred between server and client\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eMultiple rounds of query parsing can be avoided\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003c/div\u003e\n\u003cp\u003eThis can result in a considerable performance increase as compared to an application that does not use stored functions.\u003c/p\u003e\n\u003cp\u003eAlso, with \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e you can use all the data types, operators and functions of SQL.\u003c/p\u003e\n\u003c/div\u003e\n\u003cdiv class=\"sect2\" id=\"PLPGSQL-ARGS-RESULTS\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e41.1.2. Supported Argument and Result Data Types \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eFunctions written in \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e can accept as arguments any scalar or array data type supported by the server, and they can return a result of any of these types. They can also accept or return any composite type (row type) specified by name. It is also possible to declare a \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e function as accepting \u003ccode class=\"type\"\u003erecord\u003c/code\u003e, which means that any composite type will do as input, or as returning \u003ccode class=\"type\"\u003erecord\u003c/code\u003e, which means that the result is a row type whose columns are determined by specification in the calling query, as discussed in \u003ca class=\"xref\" href=\"/docs/18/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS\" title=\"7.2.1.4. Table Functions\"\u003eSection 7.2.1.4\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003e\u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e functions can be declared to accept a variable number of arguments by using the \u003ccode class=\"literal\"\u003eVARIADIC\u003c/code\u003e marker. This works exactly the same way as for SQL functions, as discussed in \u003ca class=\"xref\" href=\"/docs/18/xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS\" title=\"36.5.6. SQL Functions with Variable Numbers of Arguments\"\u003eSection 36.5.6\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003e\u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e functions can also be declared to accept and return the polymorphic types described in \u003ca class=\"xref\" href=\"/docs/18/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC\" title=\"36.2.5. Polymorphic Types\"\u003eSection 36.2.5\u003c/a\u003e, thus allowing the actual data types handled by the function to vary from call to call. Examples appear in \u003ca class=\"xref\" href=\"/docs/18/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS\" title=\"41.3.1. Declaring Function Parameters\"\u003eSection 41.3.1\u003c/a\u003e.\u003c/p\u003e\n\u003cp\u003e\u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e functions can also be declared to return a \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eset\u003c/span\u003e”\u003c/span\u003e (or table) of any data type that can be returned as a single instance. Such a function generates its output by executing \u003ccode class=\"command\"\u003eRETURN NEXT\u003c/code\u003e for each desired element of the result set, or by using \u003ccode class=\"command\"\u003eRETURN QUERY\u003c/code\u003e to output the result of evaluating a query.\u003c/p\u003e\n\u003cp\u003eFinally, a \u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e function can be declared to return \u003ccode class=\"type\"\u003evoid\u003c/code\u003e if it has no useful return value. (Alternatively, it could be written as a procedure in that case.)\u003c/p\u003e\n\u003cp\u003e\u003cspan class=\"application\"\u003ePL/pgSQL\u003c/span\u003e functions can also be declared with output parameters in place of an explicit specification of the return type. This does not add any fundamental capability to the language, but it is often convenient, especially for returning multiple values. The \u003ccode class=\"literal\"\u003eRETURNS TABLE\u003c/code\u003e notation can also be used in place of \u003ccode class=\"literal\"\u003eRETURNS SETOF\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eSpecific examples appear in \u003ca class=\"xref\" href=\"/docs/18/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS\" title=\"41.3.1. Declaring Function Parameters\"\u003eSection 41.3.1\u003c/a\u003e and \u003ca class=\"xref\" href=\"/docs/18/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING\" title=\"41.6.1. Returning from a Function\"\u003eSection 41.6.1\u003c/a\u003e.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e","related":[{"label":"plpgsql-control-structures","url":"/docs/18/plpgsql-control-structures.html"},{"label":"plpgsql-cursors","url":"/docs/18/plpgsql-cursors.html"},{"label":"plpgsql-declarations","url":"/docs/18/plpgsql-declarations.html"},{"label":"plpgsql-development-tips","url":"/docs/18/plpgsql-development-tips.html"},{"label":"plpgsql-errors-and-messages","url":"/docs/18/plpgsql-errors-and-messages.html"},{"label":"plpgsql-expressions","url":"/docs/18/plpgsql-expressions.html"},{"label":"plpgsql-implementation","url":"/docs/18/plpgsql-implementation.html"},{"label":"plpgsql-overview","url":"/docs/18/plpgsql-overview.html"},{"label":"plpgsql-porting","url":"/docs/18/plpgsql-porting.html"},{"label":"plpgsql-statements","url":"/docs/18/plpgsql-statements.html"},{"label":"plpgsql-structure","url":"/docs/18/plpgsql-structure.html"},{"label":"plpgsql-transactions","url":"/docs/18/plpgsql-transactions.html"},{"label":"plpgsql-trigger","url":"/docs/18/plpgsql-trigger.html"}],"sections":[{"code":"/* src/pl/plpgsql/src/plpgsql--1.0.sql */\n\nCREATE FUNCTION plpgsql_call_handler() RETURNS language_handler\n  LANGUAGE c AS 'MODULE_PATHNAME';\n\nCREATE FUNCTION plpgsql_inline_handler(internal) RETURNS void\n  STRICT LANGUAGE c AS 'MODULE_PATHNAME';\n\nCREATE FUNCTION plpgsql_validator(oid) RETURNS void\n  STRICT LANGUAGE c AS 'MODULE_PATHNAME';\n\nCREATE TRUSTED LANGUAGE plpgsql\n  HANDLER plpgsql_call_handler\n  INLINE plpgsql_inline_handler\n  VALIDATOR plpgsql_validator;\n\n-- The language object, but not the functions, can be owned by a non-superuser.\nALTER LANGUAGE plpgsql OWNER TO @extowner@;\n\nCOMMENT ON LANGUAGE plpgsql IS 'PL/pgSQL procedural language';","paragraphs":["This extension is shipped with the source. Its presence does not establish that the language or external runtime is installed."],"title":"Installation definition from the matching source archive"}],"tables":[]}},"RequestedLocale":"zh-Hans","Fallback":true,"Versions":["10","11","12","13","14","15","16","17","18","19","20"],"Locales":["en"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}