PL/PGSQL Function

-

PostgreSQL CREATE FUNCTION Statement:

Syntax:

CREATE FUNCTION function_name(p1 type, p2 type)
RETURNS type AS
BEGIN
-- logic
END;
LANGUAGE language_name;

Must follow:

First, specify the name of the function aftr the CREATE FUNCTION keywords.
Then, put a comma-separated list of parameters inside the parentheses following the function name.
Next, specify the return type of the function after the RETURNS keyword.
After that, place the code inside the BEGIN and END block. The function always ends with a semicolon (;) followed by the END keyword.
Finally, indicate the procedural language of the function e.g., plpgsql in case PL/pgSQL is

Example:

CREATE FUNCTION inc(val integer) RETURNS integer AS $$
BEGIN
RETURN val + 1;
END; $$
LANGUAGE PLPGSQL; 
 
PL/pgSQL Function Parameters:

Four types of function parameters
IN parameters
OUT parameters
INOUT parameters
VARIADIC parameters

PL/pgSQL IN parameters

CREATE OR REPLACE FUNCTION get_sum(
a NUMERIC,
b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
RETURN a + b;
END; $$
LANGUAGE plpgsql;
 
The get_sum() function accepts two parameters: a, and b and returns a numeric. The data types of the two parameters are NUMERIC. By default, the parameter’s type of any parameter in PostgreSQL is IN parameter. You can pass the IN parameters to the function but you cannot get them back as a part of the result.

SELECT get_sum(10,20);


PL/pgSQL OUT parameters:
         
                 The OUT parameters are defined as part of the function arguments list and are returned back as a part of the result. PostgreSQL supported the OUT parameters since version 8.1

Input:

CREATE OR REPLACE FUNCTION hi_lo(
a NUMERIC,
b NUMERIC,
c NUMERIC,
OUT hi NUMERIC,
OUT lo NUMERIC)
AS $$
BEGIN
hi := GREATEST(a,b,c);
lo := LEAST(a,b,c);
END; $$ 
LANGUAGE plpgsql;

Output:

The hi_lo function accepts 5 parameters:
Three IN parameters: a, b, c.
Two OUT parameters: hi (high) and lo (low).
Inside the function, we get the greatest and least numbers of three IN parameters using GREATEST and LEAST built-in functions. Because we use the OUT parameters, we don’t need to have a RETURN tatement. The OUT parameters are useful in a function that needs to return multiple values without defining a custom type.
The following statement calls the hi_lo function:

SELECT hi_lo(10,20,30);

PL/pgSQL INOUT parameters:

The INOUT parameter is the combination IN and OUT parameters. It means that the caller can pass the value to the function. The function then changes the argument and passes the value back as a part of the result.
The following example shows you the square function that accepts a number and returns the square of that number.

Input:

CREATE OR REPLACE FUNCTION square(
INOUT a NUMERIC)
AS $$
BEGIN
a := a * a;
END; $$
LANGUAGE plpgsql;

Output:

SELECT square(4);

PL/pgSQL VARIADIC parameters:

A PostgreSQL function can accept a variable number of arguments with one condition that all arguments have the same data type. The arguments are passed to the function as an array. See the following example:

Input:

CREATE OR REPLACE FUNCTION sum_avg(
VARIADIC list NUMERIC[],
OUT total NUMERIC,
OUT average NUMERIC)
AS $$
BEGIN
SELECT INTO total SUM(list[i])
FROM generate_subscripts(list, 1) g(i);

SELECT INTO average AVG(list[i])
FROM generate_subscripts(list, 1) g(i);

END; $$
LANGUAGE plpgsql;

The sum_avg() function accepts a list of numbers, calculates the total and average, and returns both values.

SELECT * FROM sum_avg(10,20,30);


Comments

Post a Comment

Popular posts from this blog

PostgreSQL pg_pool-II Installation and Configuration

-
Image
Pgpool-II Pgpool-II is a proxy software that sits between PostgreSQL servers and a PostgreSQL database client.  It provides the following features: 1.Connection Pooling:     Pgpool-II maintains established connections to the PostgreSQL servers, and reuses them whenever a new connection with the same properties (i.e. user name, database, protocol version, and other connection parameters if any) comes in. It reduces the connection overhead, and improves system's overall throughput.   2.Load Balancing:     If a database is replicated (because running in either replication mode or master/slave mode), performing a SELECT query on any server will return the same result. Pgpool-II takes advantage of the replication feature in order to reduce the load on each PostgreSQL server.  It does that by distributing SELECT queries among available servers, improving the system's overall throughput. In an ideal scenario, read performance could improve proportionally to the number of PostgreSQ
Read more

PostgreSQL Migration Using MTK

-
Enterprisedb Migration Toolkit: EDB Migration Toolkit is a powerful command-line tool that offers granular control of the migration process. Migration Toolkit facilitates migration of database objects and data to an EDB Postgres Advanced Server (Advanced Server) or PostgreSQL database from: Oracle MySQL Microsoft SQL Server Sybase which objects migrate to postgres Oracle Objects: Schemas   Tables    List-Partitioned Tables         Range-Partitioned Table         Constraints   Indexes   Triggers         Table Data   Views     Materialized Views         Packages         Procedures   Functions       Sequences       Users/Roles   Profiles   Object Types   Object Type Methods   Database Links     Queues       MySQL Objects: Schemas Tables Constraints Indexes Table Data Microsoft SQL Server Objects: Schemas Tables Constraints Indexes Table Data Sybase Obj
Read more

PostgreSQL Pages and Tuples

-
Image
INTERNAL LAYOUT: a tuple or an item is a synonym for a row a relation is a synonym for a table a filenode is an id which represent a reference to a table or an index. a block and page are equals and they represent a 8kb segment information the file storing the table. a heap refer to heap file . Heap files are lists of unordered records of variable size. Although sharing a similar name, heap files are different from heap data structure. CTID represent the physical location of the row version within its table. CTID is also a special column available for every tables but not visible unless specifically mentioned. It consists of a page number and the index of an item identifier. OID stands for Object Identifier. database cluster , we call a database cluster the storage area on disk. A database cluster is a collection of databases that is managed by a single instance of a running database server. Page:            Inside the data file (heap table and index, as well as the free space ma
Read more