PostgreSQL: Function and its uses


The function is the set of PostgreSQL statements that stored on the database server and can be invoked using the SQL interface. PostgreSQL function is also known as Stored Procedure (If you are familiar with SQL Server you will be aware with Stored Procedure). In PostgreSQL, procedural languages such as PL/pgSQL, C, Perl, Python, and Tcl are referred to as stored procedures.

 Syntax of PostgreSQL function


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; 


function_name: Name of the function
[OR REPLACE]: It is optional, uses it when you want to modify the function
RETURN: It specifies a data type that is same as function body is returning
function_body: The function_body contains the executable parts(PostgreSQL statements).
plpgsql: It specifies the name of the language in which the function is implemented.

For the illustration, see the following example.

1- Create a Product table

CREATE TABLE public.product
(
    id serial NOT NULL,
    brand text COLLATE pg_catalog."default",
    category text COLLATE pg_catalog."default",
    qty integer
)



2- Insert some records

INSERT INTO product(brand,category,qty)
  VALUES('Arrow','Cloths',3000);

INSERT INTO product(brand,category,qty)
  VALUES('Samsung','Mobile',4500);

INSERT INTO product(brand,category,qty)
  VALUES('iPad','Tablet',2000);

INSERT INTO product(brand,category,qty)
  VALUES('Prestige','Kitchen',200);


3- Create a function that will return the total product quantity

CREATE OR REPLACE FUNCTION totalProductQuantity() 
RETURNS integer AS $totalQty$ 
declare 
    sumQty integer; 
BEGIN 
   SELECT SUM(Qty) into sumQty FROM Product; 
   RETURN sumQty; 
END; 
$totalQty$ LANGUAGE plpgsql;


4- If you got the following message then your function is ready to invoke using the SQL interface.
Success Message:
CREATE FUNCTION
Query returned successfully in 117 msec.


5- Invoke the function as below and see the result:

select * from totalProductQuantity();


Result:
                totalProductQuantity
                970

5- If you are using pgAdmin then you can see under the functions "totalProductQuantity"

Example of a function as a return table type

1- I want to result set of all records using function, then you can see the following example.

CREATE OR REPLACE FUNCTION GetAllProducts() 
RETURNS TABLE(prod_id integer, brand text, category text, qty integer)
AS $BODY$
BEGIN          
RETURN QUERY SELECT p.id, p.brand, p.category, p.qty FROM Product p;
END; 
$BODY$ LANGUAGE plpgsql;


2- Execute the function

select * from GetAllProducts();

Result:

Example of a parameterized function

1- I want to pass the parameter in function and result set accordingly, suppose I want to search product on the Brand basis.

CREATE OR REPLACE FUNCTION SearchByBrand(brandname text) 
RETURNS TABLE(prod_id integer, brand text, category text, qty integer)
AS $BODY$
BEGIN          
       RETURN QUERY SELECT p.id, p.brand, p.category, p.qty FROM Product p
                           WHERE p.brand like'%'||brandname||'%';
END; 
$BODY$ LANGUAGE plpgsql;


2- Now, run the function as below

select * from SearchByBrand('Arrow');

Result:

Fatal Error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails.

Problem: Sometimes, we get the following error when trying to make a connection in Rails.  FATAL ERROR: Peer authentication failed for us...