PostgreSQL: What are the Differences between SQL and PL/pgSQL language in Function

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Most of the new PostgreSQL comers have doubt for SQL and PL/pgSQL language of PostgreSQL.

In the PostgreSQL, We can create function mainly using three languages like: SQL, PL/pgSQL, C programming.

PostgreSQL also supported lot many languages like: Perl, Python, Ruby, Java and others.

Most of the PostgreSQL Database Developers are using SQL and PL/pgSQL language.

What are the main differences between SQL and PL/pgSQL?

SQL Language:

• This is a pure SQL only.
• SQL language executes an arbitrary list of SQL statements and returning the result of the last query in the list.
• SQL provides a very simple scalar query and It is not supported any query cache or query plan mechanism.
• It is portable and easy to learn.
• We cannot use any transaction command like: COMMIT, ROLLBACK and SAVEPOINT
• When we do not require to return any value, We can define SQL functions as void.
• For example,
  
  1 2 3

  CREATE FUNCTION fn_UpdateStudents() RETURNS void AS 'UPDATE tbl_Students SET StudName=''Anvesh'' WHERE 1=2' LANGUAGE SQL;

• We cannot use any VACUUM related commands in SQL Function.
• We cannot use any prepared statements in SQL Function.
• We can use SQL language for simple scalar queries because It does not support all other Procedural language feature like PL/pgSQL.(Features like: variables, loops, cursors, transaction command, query plan, query cache)
• One of the disadvantage is, every SQL statement must be executed individually by the database server and all other queries have to wait for it to be processed.

PL/pgSQL Language:

• PL/pgSQL is a procedural language, we can execute a block of statement inside the database server.
• We can eliminate extra round trips between server and client.
• We can use feature like: variable, dynamic query, cursor, trigger, transaction command and others.
• Like SQL Language, we can also set a return value in PL/pgSQL.
• We have N number of ways to write PL/pgSQL block for increasing the query performance.
• All query plans can be cached and we can use as a prepared statement.