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

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,
  • 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.
Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of