Questions And Answers

More Tutorials

postgreSQL Programming with PL pgSQL

PL/pgSQL is PostgreSQL's built-in programming language for writing functions which run within the database itself, known as stored procedures in other databases. It extends SQL with loops, conditionals, and return types. Though its syntax may be strange to many developers it is much faster than anything running on the application server because the overhead of connecting to the database is eliminated, which is particularly useful when you would otherwise need to execute a query, wait for the result, and submit another query.

Though many other procedural languages exist for PostgreSQL, such as PL/Python, PL/Perl, and PLV8, PL/pgSQL is a common starting point for developers who want to write their first PostgreSQL function because its syntax builds on SQL. It is also similar to PL/SQL, Oracle's native procedural language, so any developer familiar with PL/SQL will find the language familiar, and any developer who intends to develop Oracle applications in the future but wants to start with a free database can transition from PL/pgSQL to PL/SQL with relative ease.

It should be emphasized that other procedural languages exist and PL/pgSQL is not necessarily superior to them in any way, including speed, but examples in PL/pgSQL can serve as a common reference point for other languages used for writing PostgreSQL functions. PL/pgSQL has the most tutorials and books of all the PLs and can be a springboard to learning the languages with
less documentation.


Basic PL/pgSQL Function

A simple PL/pgSQL function:

CREATE FUNCTION active_subscribers() RETURNS bigint AS $$
 -- variable for the following BEGIN ... END block
 subscribers integer;
 -- SELECT must always be used with INTO
 SELECT COUNT(user_id) INTO subscribers FROM users WHERE subscribed;
 -- function result
 RETURN subscribers;
 -- return NULL if table "users" does not exist
 WHEN undefined_table
$$ LANGUAGE plpgsql;

This could have been achieved with just the SQL statement but demonstrates the basic structure of a function.
To execute the function do:

select active_subscribers();

PL/pgSQL Syntax

CREATE [OR REPLACE] FUNCTION functionName (someParameter 'parameterType')
AS $_block_name_$
 --declare something
 --do something
 --return something
LANGUAGE plpgsql;


Options for returning in a PL/pgSQL function:
• Datatype List of all datatypes
• Table(column_name column_type, ...)
• Setof 'Datatype' or 'table_column'

custom exceptions

creating custom exception 'P2222':
create or replace function s164() returns void as
raise exception using message = 'S 164', detail = 'D 164', hint = 'H 164', errcode = 'P2222';
$$ language plpgsql
creating custom exception not assigning errm:
create or replace function s165() returns void as
raise exception '%','nothing specified';
$$ language plpgsql


t=# do
 _t text;
 perform s165();
 exception when SQLSTATE 'P0001' then raise info '%','state P0001 caught: '||SQLERRM;
 perform s164();
INFO: state P0001 caught: nothing specified
ERROR: S 164
HINT: H 164
CONTEXT: SQL statement "SELECT s164()"
PL/pgSQL function inline_code_block line 7 at PERFORM

here custom P0001 processed, and P2222, not, aborting the execution.


In this page (written and validated by ) you learned about postgreSQL Programming with PL pgSQL . What's Next? If you are interested in completing postgreSQL tutorial, your next topic will be learning about: PostgreSQL Recursive queries.

Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue:

Share On:

Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using, you agree to have read and accepted our terms of use, cookies and privacy policy.