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:

PostgreSQL - Upsert query using ON CONFLICT clause

I want to insert data from a source that can contain duplicate data or data that may exist into the table, so simple I want to add data that do not exist in the table and update the table if data exist. In simple language, I wish to upsert the data in a table from the data-source, data source means CSV, excel, another table, etc. For example, suppose we have an Inventory table, and from the CSV source, we need to insert data like prod_name, KPI, brand, qty, price, but it maybe some products already exist in the Inventory table, and we must update those data.

Syntax 


INSERT INTO TableName(ColumnList) VALUES(ValueList)
ON CONFLICT target action;

PostgreSQL - ON CONFLICT clause example

ON CONFLICT clause may help you to resolve the upsert issue. 

create table inventory
(
  id serial not null,
  prod_name text,
  kpi text,
  qty integer,
  price decimal,
  unique(kpi)
);

--added some records to inventory
insert into inventory (prod_name, kpi, qty, price )
 values('Prod1','prod1_kpi', 20, 100),
          ('Prod2','prod2_kpi', 30, 200);


select * from inventory order by id asc;
Result: 


Now, we will use the ON CONFLICT clause to upsert data into the Inventory table.

insert into inventory (prod_name, kpi, qty, price )
 values('Prod3','prod3_kpi', 12, 122),
          ('Prod4','prod4_kpi', 28, 234),
          ('Prod1','prod1_kpi', 100, 110)--- It is existing product
          ON CONFLICT(kpi)
          do update set qty=excluded.qty, price =excluded.price;


select * from inventory order by id asc;
Result:  

('Prod1','prod1_kpi', 100, 110)--- It is existing product so it updated in invetory as you see in above result set.
('Prod3','prod3_kpi', 12, 122),('Prod4','prod4_kpi', 28, 234) --these data is not duplicate so added in inventory table



PostgreSQL - CrossTab Queries using tablefunc contrib

PostgreSQL statement uses the function crosstab to pivot the table records, pivot means I want to convert the rows to the column of particular column's value and want to the others column value respectively of converted rows.

PostgreSQL- CROSSTAB example

Suppose we have the following table

CREATE TABLE product
(
  id serial not null,
  brand text,
  category text,
  qty integer
);
-- 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);


Now, select query
SELECT * FROM product;
Result:
id
brand
category
qty
1
"Arrow"
"Cloths"
3000
2
"Samsung"
"Mobile"
4500
3
"iPad"
"Tablet"
2000
4
"Prestige"
"Kitchen"
200

But expected result as below
Result:
brand
Cloths
mobile
Tablet
kitchen
"Arrow"
3000



"iPad"


2000

"Prestige"



200
"Samsung"

4500



Using CROSSTAB we can achieve the pivot goal, se the following query

SELECT *
FROM   crosstab(
   'SELECT brand, category, qty
    FROM   product
    ORDER  BY 1,2',
                $$VALUES ('Cloths'::text), ('Mobile'),('Tablet'),('Kitchen')$$
   ) AS ct (brand text, Cloths int, Mobile int, Tablet int, Kitchen int);

 Result:
brand
Cloths
Mobile
Tablet
kitchen
"Arrow"
3000



"iPad"


2000

"Prestige"



200
"Samsung"

4500



Note: If you are getting the following error:
ERROR:  function crosstab(unknown, unknown) does not exist
LINE 2: FROM   crosstab(
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 17
The above error will remove executing the following PostgreSQL statement

CREATE EXTENSION IF NOT EXISTS tablefunc;

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