{"id":1268,"date":"2024-12-25T15:30:51","date_gmt":"2024-12-25T08:30:51","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=1268"},"modified":"2025-01-12T13:00:39","modified_gmt":"2025-01-12T06:00:39","slug":"postgresql-create-function","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-create-function\/","title":{"rendered":"PostgreSQL CREATE FUNCTION Statement"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you&#8217;ll learn how to use the PostgreSQL <code>CREATE FUNCTION<\/code> statement to create a user-defined function.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='getting-started-with-the-postgresql-create-function-statement'>Getting Started with the PostgreSQL CREATE FUNCTION statement <a href=\"#getting-started-with-the-postgresql-create-function-statement\" class=\"anchor\" id=\"getting-started-with-the-postgresql-create-function-statement\" title=\"Anchor for Getting Started with the PostgreSQL CREATE FUNCTION statement\">#<\/a><\/h2>\n\n\n\n<p>A function is a reusable piece of code that performs a specific task. For example, the <code><a href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-concat\/\">CONCAT()<\/a><\/code> function allows you to concatenate two or more strings into one string. The <code>CONCAT()<\/code> is a built-in function provided by PostgreSQL.<\/p>\n\n\n\n<p>PostgreSQL allows you to create a new function using the <code>CREATE FUNCTION<\/code> statement. This function is called a user-defined function because it is created by you and other developers, not provided by PostgreSQL out of the box.<\/p>\n\n\n\n<p>A user-defined function is a custom function that does a specific task like a built-in function.<\/p>\n\n\n\n<p>This function takes input parameters, executes one or more SQL statements, and returns one or multiple values.<\/p>\n\n\n\n<p>Typically, you write a user-defined function to encapsulate complex logic and make it reusable within the database.<\/p>\n\n\n\n<p>The following shows the basic syntax of the <code>CREATE FUNCTION<\/code> statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">CREATE<\/span> &#91;<span class=\"hljs-keyword\">OR REPLACE<\/span>] <span class=\"hljs-keyword\">FUNCTION<\/span> function_name (parameters)\n<span class=\"hljs-keyword\">RETURNS<\/span> return_type \n<span class=\"hljs-keyword\">AS<\/span> \n$$<span class=\"pgsql\">\n\u00a0\u00a0<span class=\"hljs-comment\">-- function body<\/span>\n$$<\/span> \n<span class=\"hljs-keyword\">LANGUAGE<\/span> <span class=\"hljs-keyword\">SQL<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>CREATE [OR REPLACE] FUNCTION<\/code> instructs PostgreSQL to create a new function. If the function name already exists, the <code>OR REPLACE<\/code> option replaces it.<\/li>\n\n\n\n<li><code>function_name (parameters)<\/code> is the function name followed by parameters.<\/li>\n\n\n\n<li><code>RETURNS return_type<\/code> specifies the data type of a value the function will return. If the function returns no value, the <code>return_type<\/code> is <code>VOID<\/code>.<\/li>\n\n\n\n<li><code>AS $$ ... $$<\/code> indicates the function body enclosed within dollar-quoted strings (<code>$$<\/code>).<\/li>\n\n\n\n<li><code>LANGUAGE SQL<\/code> specifies that the function is using <code>SQL<\/code>. Besides SQL, you can use <a href=\"https:\/\/wiki.postgresql.org\/wiki\/PL_Matrix\" target=\"_blank\" rel=\"noreferrer noopener\">other procedure programming languages<\/a> such as <a href=\"https:\/\/www.pgtutorial.com\/plpgsql\/\">PL\/pgSQL<\/a>.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id='dollar-quoted-string-literal'>Dollar-Quoted String Literal ($$) <a href=\"#dollar-quoted-string-literal\" class=\"anchor\" id=\"dollar-quoted-string-literal\" title=\"Anchor for Dollar-Quoted String Literal ($$)\">#<\/a><\/h3>\n\n\n\n<p>PostgreSQL requires a function body as a string. If you use regular strings, you need to escape quotes and other special characters. <\/p>\n\n\n\n<p>To make the function body more readable, PostgreSQL offers dollar-quoted string syntax:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">$tag$<span class=\"perl\">&lt;string_constant&gt;$tag$<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The <code>tag<\/code> is an optional identifier.<\/li>\n\n\n\n<li>Between <code>$tag$<\/code>s, you can place a string constant, which is the function body. And you don&#8217;t have to  escape quotes or special characters.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id='calling-a-user-defined-function'>Calling a user-defined function <a href=\"#calling-a-user-defined-function\" class=\"anchor\" id=\"calling-a-user-defined-function\" title=\"Anchor for Calling a user-defined function\">#<\/a><\/h3>\n\n\n\n<p>To call a user-defined function, you use the <code>SELECT<\/code> statement, followed by the function name and arguments:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span> function_name(arguments);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax, the <code>arguments<\/code> can be one or more arguments corresponding to parameters defined in the <code>function_name<\/code>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='postgresql-create-function-statement-examples'>PostgreSQL CREATE FUNCTION statement examples <a href=\"#postgresql-create-function-statement-examples\" class=\"anchor\" id=\"postgresql-create-function-statement-examples\" title=\"Anchor for PostgreSQL CREATE FUNCTION statement examples\">#<\/a><\/h2>\n\n\n\n<p>Let&#8217;s explore some examples of creating new user-defined functions.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='creating-a-function-that-returns-no-value'>Creating a function that returns no value <a href=\"#creating-a-function-that-returns-no-value\" class=\"anchor\" id=\"creating-a-function-that-returns-no-value\" title=\"Anchor for Creating a function that returns no value\">#<\/a><\/h3>\n\n\n\n<p>The following example uses the <code>CREATE FUNCTION<\/code> statement to create a function that adds a new warehouse with name and location:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">OR REPLACE<\/span> <span class=\"hljs-keyword\">FUNCTION<\/span> add_warehouse (\n  <span class=\"hljs-type\">name<\/span> <span class=\"hljs-type\">VARCHAR<\/span>, \n  <span class=\"hljs-keyword\">location<\/span> <span class=\"hljs-type\">VARCHAR<\/span>\n) \n<span class=\"hljs-keyword\">RETURNS<\/span> <span class=\"hljs-type\">VOID<\/span> \n<span class=\"hljs-keyword\">AS<\/span> \n$$<span class=\"pgsql\">\n\u00a0 \u00a0\u00a0<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> warehouses(warehouse_name, address)\n\u00a0 \u00a0\u00a0<span class=\"hljs-keyword\">VALUES<\/span>(<span class=\"hljs-type\">name<\/span>,<span class=\"hljs-keyword\">location<\/span>);\n$$<\/span> \n<span class=\"hljs-keyword\">LANGUAGE<\/span> <span class=\"hljs-keyword\">SQL<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=Q1JFQVRFIE9SIFJFUExBQ0UgRlVOQ1RJT04gYWRkX3dhcmVob3VzZSAoIG5hbWUgVkFSQ0hBUiwgbG9jYXRpb24gVkFSQ0hBUiApIFJFVFVSTlMgVk9JRCBBUyAkJCBJTlNFUlQgSU5UTyB3YXJlaG91c2VzKHdhcmVob3VzZV9uYW1lLCBhZGRyZXNzKSBWQUxVRVMobmFtZSxsb2NhdGlvbik7ICQkIExBTkdVQUdFIFNRTDs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>The <code>add_warehouse<\/code> function takes two parameters:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>name<\/code>with the type <code><a href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-varchar\/\">VARCHAR<\/a><\/code>, which indicates the warehouse name.<\/li>\n\n\n\n<li><code>location<\/code> has the same <code>VARCHAR<\/code> data type, which indicates the warehouse&#8217;s address.<\/li>\n<\/ul>\n\n\n\n<p>You cannot use the parameter names the same as the column names of tables used inside the function. If you do so, PostgreSQL will issue an error. The reason is that PostgreSQL will confuse the parameters and column names.<\/p>\n\n\n\n<p>The <code>RETURNS VOID<\/code> indicates the <code>add_warehouse<\/code> function does not return any value.<\/p>\n\n\n\n<p class=\"note\">Notice that the <code>RETURNS<\/code> keyword includes the letter <code>S<\/code>, not <code>RETURN<\/code>.<\/p>\n\n\n\n<p>The <code>AS<\/code> keyword tells the start of the function&#8217;s body.<\/p>\n\n\n\n<p>Between the <code>$$<\/code> is a dollar-quoted string that represents the function body.<\/p>\n\n\n\n<p>Inside the function body, we use an <code><a href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-insert\/\">INSERT<\/a><\/code> statement that inserts a new row into the <code>warehouses<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> warehouses (warehouse_name, address)\n<span class=\"hljs-keyword\">VALUES<\/span>(<span class=\"hljs-type\">name<\/span>, <span class=\"hljs-keyword\">location<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In the <code>VALUES<\/code> clause, we use the parameters <code>name<\/code> and <code>location<\/code>.<\/p>\n\n\n\n<p>The <code>LANGUAGE SQL<\/code> clause indicates that the function uses SQL.<\/p>\n\n\n\n<p class=\"note\">Note that you can write all the code in lowercase, including keywords like <code>create or replace function<\/code>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='calling-the-add_warehouse-function'>Calling the add_warehouse function <a href=\"#calling-the-add_warehouse-function\" class=\"anchor\" id=\"calling-the-add_warehouse-function\" title=\"Anchor for Calling the add_warehouse function\">#<\/a><\/h3>\n\n\n\n<p>First, call the <code>add_warehouse<\/code> function to add a new warehouse to the <code>warehouses<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span>\n  add_warehouse (\n    <span class=\"hljs-string\">'San Mateo'<\/span>,\n    <span class=\"hljs-string\">'2222 S Delaware St, San Mateo, CA 94403'<\/span>\n  );<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=U0VMRUNUIGFkZF93YXJlaG91c2UgKCAnU2FuIE1hdGVvJywgJzIyMjIgUyBEZWxhd2FyZSBTdCwgU2FuIE1hdGVvLCBDQSA5NDQwMycgKTs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Second, retrieve data from the <code>warehouses<\/code> table to verify the function call:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span>\n  warehouse_name,\n  address\n<span class=\"hljs-keyword\">FROM<\/span>\n  warehouses;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=U0VMRUNUIHdhcmVob3VzZV9uYW1lLCBhZGRyZXNzIEZST00gd2FyZWhvdXNlczs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">     warehouse_name      |                     address\n<span class=\"hljs-comment\">-------------------------+-------------------------------------------------<\/span>\n San Jose Warehouse      | <span class=\"hljs-number\">205<\/span> E Alma Ave, San Jose, CA <span class=\"hljs-number\">95112<\/span>\n San Francisco Warehouse | <span class=\"hljs-number\">233<\/span> E Harris Ave, South San Francisco, CA <span class=\"hljs-number\">94080<\/span>\n Los Angeles Warehouse   | <span class=\"hljs-number\">1919<\/span> Vineburn Avenue, Los Angeles, CA <span class=\"hljs-number\">90032<\/span>\n San Mateo               | <span class=\"hljs-number\">2222<\/span> S Delaware St, San Mateo, CA <span class=\"hljs-number\">94403<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The output shows the new warehouse in the <code>warehouses<\/code> table.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='creating-a-function-that-returns-a-single-value'>Creating a function that returns a single value <a href=\"#creating-a-function-that-returns-a-single-value\" class=\"anchor\" id=\"creating-a-function-that-returns-a-single-value\" title=\"Anchor for Creating a function that returns a single value\">#<\/a><\/h3>\n\n\n\n<p>The following example uses the <code>CREATE FUNCTION<\/code> statement to create a <code>get_inventory_amount()<\/code> that returns the total inventory amount:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">OR REPLACE<\/span> <span class=\"hljs-keyword\">FUNCTION<\/span> get_inventory_amount () \n  <span class=\"hljs-keyword\">RETURNS<\/span> <span class=\"hljs-type\">DEC<\/span> \n<span class=\"hljs-keyword\">AS<\/span> \n$$<span class=\"pgsql\">\n<span class=\"hljs-keyword\">SELECT<\/span>\n  SUM(quantity * price)\n<span class=\"hljs-keyword\">FROM<\/span>\n  inventories\n  <span class=\"hljs-keyword\">JOIN<\/span> products <span class=\"hljs-keyword\">USING<\/span> (product_id);\n$$<\/span> \n<span class=\"hljs-keyword\">LANGUAGE<\/span> <span class=\"hljs-keyword\">SQL<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=Q1JFQVRFIE9SIFJFUExBQ0UgRlVOQ1RJT04gZ2V0X2ludmVudG9yeV9hbW91bnQgKCkgUkVUVVJOUyBERUMgQVMgJCQgU0VMRUNUIFNVTShxdWFudGl0eSAqIHByaWNlKSBGUk9NIGludmVudG9yaWVzIEpPSU4gcHJvZHVjdHMgVVNJTkcgKHByb2R1Y3RfaWQpOyAkJCBMQU5HVUFHRSBTUUw7\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>In this example, the <code>get_inventory_amount()<\/code> function has no parameter and returns a number with the type <code>DECIMAL<\/code> or <code>DEC<\/code> in short.<\/p>\n\n\n\n<p>The <code>get_inventory_amount<\/code> function executes an SQL statement that <a href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-inner-join\/\">joins<\/a> the <code>inventories<\/code> and <code>products<\/code> tables and calculates the total inventory amount using the <a href=\"https:\/\/www.pgtutorial.com\/postgresql-aggregate-functions\/postgresql-sum\/\"><code>SUM<\/code> aggregate function<\/a>.<\/p>\n\n\n\n<p>The following statement calls the <code>get_inventory_amount()<\/code> function:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span>\n  get_inventory_amount () <span class=\"hljs-keyword\">AS<\/span> inventory_amount;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=U0VMRUNUIGdldF9pbnZlbnRvcnlfYW1vdW50ICgpIEFTIGludmVudG9yeV9hbW91bnQ7\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"> inventory_amount\n<span class=\"hljs-comment\">------------------<\/span>\n       <span class=\"hljs-number\">6843946.40<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\" id='creating-a-function-that-returns-a-table'>Creating a function that returns a table <a href=\"#creating-a-function-that-returns-a-table\" class=\"anchor\" id=\"creating-a-function-that-returns-a-table\" title=\"Anchor for Creating a function that returns a table\">#<\/a><\/h3>\n\n\n\n<p>The following statement uses the <code>CREATE TABLE<\/code> statement to create a function <code>get_inventory_amount_by_warehouses<\/code> that returns the inventory amounts by warehouses:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">OR REPLACE<\/span> <span class=\"hljs-keyword\">FUNCTION<\/span> get_inventory_amount_by_warehouses () \n  <span class=\"hljs-keyword\">RETURNS<\/span> <span class=\"hljs-keyword\">TABLE<\/span> (\n       warehouse_name <span class=\"hljs-type\">VARCHAR<\/span>, \n       amount <span class=\"hljs-type\">DEC<\/span>\n  ) \n<span class=\"hljs-keyword\">AS<\/span> \n$$<span class=\"pgsql\">\n<span class=\"hljs-keyword\">SELECT<\/span>\n  warehouse_name, \n  SUM(quantity * price) <span class=\"hljs-keyword\">AS<\/span> amount\n<span class=\"hljs-keyword\">FROM<\/span>\n  inventories\n  <span class=\"hljs-keyword\">JOIN<\/span> products <span class=\"hljs-keyword\">USING<\/span> (product_id)\n  <span class=\"hljs-keyword\">JOIN<\/span> warehouses <span class=\"hljs-keyword\">USING<\/span>(warehouse_id)\n<span class=\"hljs-keyword\">GROUP<\/span> <span class=\"hljs-keyword\">BY<\/span> warehouse_name\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> warehouse_name;\n$$<\/span> \n<span class=\"hljs-keyword\">LANGUAGE<\/span> <span class=\"hljs-keyword\">SQL<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=Q1JFQVRFIE9SIFJFUExBQ0UgRlVOQ1RJT04gZ2V0X2ludmVudG9yeV9hbW91bnRfYnlfd2FyZWhvdXNlcyAoKSBSRVRVUk5TIFRBQkxFICggd2FyZWhvdXNlX25hbWUgVkFSQ0hBUiwgYW1vdW50IERFQyApIEFTICQkIFNFTEVDVCB3YXJlaG91c2VfbmFtZSwgU1VNKHF1YW50aXR5ICogcHJpY2UpIEFTIGFtb3VudCBGUk9NIGludmVudG9yaWVzIEpPSU4gcHJvZHVjdHMgVVNJTkcgKHByb2R1Y3RfaWQpIEpPSU4gd2FyZWhvdXNlcyBVU0lORyh3YXJlaG91c2VfaWQpIEdST1VQIEJZIHdhcmVob3VzZV9uYW1lIE9SREVSIEJZIHdhcmVob3VzZV9uYW1lOyAkJCBMQU5HVUFHRSBTUUw7\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>The <code>get_inventory_amount_by_warehouses<\/code> function&#8217;s return type is a table with two columns:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>warehouse_name<\/code><\/li>\n\n\n\n<li><code>amount<\/code><\/li>\n<\/ul>\n\n\n\n<p>The function executes a query that returns a result set with the columns corresponding to the table column in the <code>RETURNS<\/code> clause.<\/p>\n\n\n\n<p>The following statement calls the <code>get_inventory_amount_by_warehouses()<\/code> function:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> get_inventory_amount_by_warehouses();<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=U0VMRUNUICogRlJPTSBnZXRfaW52ZW50b3J5X2Ftb3VudF9ieV93YXJlaG91c2VzKCk7\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-14\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">     warehouse_name      |   amount\n<span class=\"hljs-comment\">-------------------------+------------<\/span>\n Los Angeles Warehouse   | <span class=\"hljs-number\">2379982.20<\/span>\n San Francisco Warehouse | <span class=\"hljs-number\">2419483.10<\/span>\n San Jose Warehouse      | <span class=\"hljs-number\">2044481.10<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-14\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Since the function returns a table, you can place it in the <code>FROM<\/code> clause.<\/p>\n\n\n\n<p>If you call the function in the <code>SELECT<\/code> clause, it&#8217;ll return a table of three rows. Each row contains a value of a composite type that consists of two fields <code>warehouse_name<\/code> and <code>amount<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-15\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span>\n  get_inventory_amount_by_warehouses ();<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-15\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=U0VMRUNUIGdldF9pbnZlbnRvcnlfYW1vdW50X2J5X3dhcmVob3VzZXMgKCk7\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-16\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">   get_inventory_amount_by_warehouses\n<span class=\"hljs-comment\">----------------------------------------<\/span>\n (\"Los Angeles Warehouse\",<span class=\"hljs-number\">2379982.20<\/span>)\n (\"San Francisco Warehouse\",<span class=\"hljs-number\">2419483.10<\/span>)\n (\"San Jose Warehouse\",<span class=\"hljs-number\">2044481.10<\/span>)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-16\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\" id='creating-a-function-that-returns-a-value-of-a-composite-type'>Creating a function that returns a value of a composite type. <a href=\"#creating-a-function-that-returns-a-value-of-a-composite-type\" class=\"anchor\" id=\"creating-a-function-that-returns-a-value-of-a-composite-type\" title=\"Anchor for Creating a function that returns a value of a composite type.\">#<\/a><\/h3>\n\n\n\n<p>In PostgreSQL, you can define a composite type that groups multiple fields into a single unit. The PostgreSQL composite type works like a record or struct in other programming languages.<\/p>\n\n\n\n<p>First, define a composite type called <code>product_info<\/code> that consists of the product name and price:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-17\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TYPE<\/span> product_info <span class=\"hljs-keyword\">AS<\/span> (\n    product_name <span class=\"hljs-type\">VARCHAR<\/span>, \n    price <span class=\"hljs-type\">DECIMAL<\/span>\n);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-17\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=Q1JFQVRFIFRZUEUgcHJvZHVjdF9pbmZvIEFTICggcHJvZHVjdF9uYW1lIFZBUkNIQVIsIHByaWNlIERFQ0lNQUwgKTs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Second, create a function that returns a value of the <code>product_info<\/code> composite type:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-18\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">OR REPLACE<\/span> <span class=\"hljs-keyword\">FUNCTION<\/span> get_product_info (id <span class=\"hljs-type\">INT<\/span>) \n  <span class=\"hljs-keyword\">RETURNS<\/span> product_info \n<span class=\"hljs-keyword\">AS<\/span> \n$$<span class=\"pgsql\">\n<span class=\"hljs-keyword\">SELECT<\/span> product_name, price\n<span class=\"hljs-keyword\">FROM<\/span> products\n<span class=\"hljs-keyword\">WHERE<\/span> product_id = id;\n$$<\/span> \n<span class=\"hljs-keyword\">LANGUAGE<\/span> <span class=\"hljs-keyword\">SQL<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-18\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=Q1JFQVRFIE9SIFJFUExBQ0UgRlVOQ1RJT04gZ2V0X3Byb2R1Y3RfaW5mbyAoaWQgSU5UKSBSRVRVUk5TIHByb2R1Y3RfaW5mbyBBUyAkJCBTRUxFQ1QgcHJvZHVjdF9uYW1lLCBwcmljZSBGUk9NIHByb2R1Y3RzIFdIRVJFIHByb2R1Y3RfaWQgPSBpZDsgJCQgTEFOR1VBR0UgU1FMOw%3D%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>In this example, the function <code>get_product_info<\/code> returns a value of the composite type <code>product_info<\/code>, which includes <code>product_name<\/code> and <code>price<\/code>.<\/p>\n\n\n\n<p>Third, call the <code>get_product_info<\/code> to retrieve the information of the product id 1:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-19\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span> get_product_info(<span class=\"hljs-number\">1<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-19\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=U0VMRUNUIGdldF9wcm9kdWN0X2luZm8oMSk7\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-20\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">       get_product_info\n<span class=\"hljs-comment\">-------------------------------<\/span>\n (\"Samsung Galaxy S24\",<span class=\"hljs-number\">999.99<\/span>)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-20\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The statement returns a value of the <code>product_info<\/code> composite type.<\/p>\n\n\n\n<p>To select fields from this composite value, you can place the function call in the <code>FROM<\/code> clause:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-21\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> get_product_info(<span class=\"hljs-number\">1<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-21\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=U0VMRUNUICogRlJPTSBnZXRfcHJvZHVjdF9pbmZvKDEpOw%3D%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-22\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">    product_name    | price\n<span class=\"hljs-comment\">--------------------+--------<\/span>\n Samsung Galaxy S24 | <span class=\"hljs-number\">999.99<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-22\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='summary'>Summary <a href=\"#summary\" class=\"anchor\" id=\"summary\" title=\"Anchor for Summary\">#<\/a><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use the <code>CREATE FUNCTION<\/code> statement to create a new user-defined function.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id='quiz'>Quiz <a href=\"#quiz\" class=\"anchor\" id=\"quiz\" title=\"Anchor for Quiz\">#<\/a><\/h2>\n\n\n\n<iframe loading=\"lazy\"\n  name=\"quiz\"\n  src=\"\/quiz\/?quiz=create-function\"\n  height=\"700\"\n  width=\"600\"\n  class=\"iframe\"\n><\/iframe>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful ?<\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"1268\"\n\t\t\t\tdata-post-url=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-create-function\/\"\n\t\t\t\tdata-post-title=\"PostgreSQL CREATE FUNCTION Statement\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"1268\"\n\t\t\t\tdata-post-url=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-create-function\/\"\n\t\t\t\tdata-post-title=\"PostgreSQL CREATE FUNCTION Statement\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Summary: in this tutorial, you&#8217;ll learn how to use the PostgreSQL CREATE FUNCTION statement to create a user-defined function. Getting Started with the PostgreSQL CREATE FUNCTION statement # A function is a reusable piece of code that performs a specific task. For example, the CONCAT() function allows you to concatenate two or more strings into [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":13,"menu_order":78,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-1268","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>PostgreSQL CREATE FUNCTION Statement<\/title>\n<meta name=\"description\" content=\"In this tutorial, you&#039;ll learn how to use the PostgreSQL CREATE FUNCTION statement to create a user-defined function.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-create-function\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL CREATE FUNCTION Statement\" \/>\n<meta property=\"og:description\" content=\"In this tutorial, you&#039;ll learn how to use the PostgreSQL CREATE FUNCTION statement to create a user-defined function.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-create-function\/\" \/>\n<meta property=\"og:site_name\" content=\"PostgreSQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2025-01-12T06:00:39+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/postgresql-create-function\\\/\",\"url\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/postgresql-create-function\\\/\",\"name\":\"PostgreSQL CREATE FUNCTION Statement\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/#website\"},\"datePublished\":\"2024-12-25T08:30:51+00:00\",\"dateModified\":\"2025-01-12T06:00:39+00:00\",\"description\":\"In this tutorial, you'll learn how to use the PostgreSQL CREATE FUNCTION statement to create a user-defined function.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/postgresql-create-function\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/postgresql-create-function\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/postgresql-create-function\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.pgtutorial.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL Tutorial\",\"item\":\"https:\\\/\\\/www.pgtutorial.com\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"PostgreSQL CREATE FUNCTION Statement\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/#website\",\"url\":\"https:\\\/\\\/www.pgtutorial.com\\\/\",\"name\":\"PostgreSQL Tutorial\",\"description\":\"Learn PostgreSQL from Scratch\",\"alternateName\":\"PostgreSQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.pgtutorial.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PostgreSQL CREATE FUNCTION Statement","description":"In this tutorial, you'll learn how to use the PostgreSQL CREATE FUNCTION statement to create a user-defined function.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-create-function\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL CREATE FUNCTION Statement","og_description":"In this tutorial, you'll learn how to use the PostgreSQL CREATE FUNCTION statement to create a user-defined function.","og_url":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-create-function\/","og_site_name":"PostgreSQL Tutorial","article_modified_time":"2025-01-12T06:00:39+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-create-function\/","url":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-create-function\/","name":"PostgreSQL CREATE FUNCTION Statement","isPartOf":{"@id":"https:\/\/www.pgtutorial.com\/#website"},"datePublished":"2024-12-25T08:30:51+00:00","dateModified":"2025-01-12T06:00:39+00:00","description":"In this tutorial, you'll learn how to use the PostgreSQL CREATE FUNCTION statement to create a user-defined function.","breadcrumb":{"@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-create-function\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-create-function\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-create-function\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.pgtutorial.com\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL Tutorial","item":"https:\/\/www.pgtutorial.com\/"},{"@type":"ListItem","position":3,"name":"PostgreSQL CREATE FUNCTION Statement"}]},{"@type":"WebSite","@id":"https:\/\/www.pgtutorial.com\/#website","url":"https:\/\/www.pgtutorial.com\/","name":"PostgreSQL Tutorial","description":"Learn PostgreSQL from Scratch","alternateName":"PostgreSQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.pgtutorial.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/1268","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/comments?post=1268"}],"version-history":[{"count":8,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/1268\/revisions"}],"predecessor-version":[{"id":1652,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/1268\/revisions\/1652"}],"up":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/13"}],"wp:attachment":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/media?parent=1268"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}