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.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.