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?
- 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,
123CREATE 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 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.