What is the purpose of pgScript in PostgreSQL?

Question

I have failed to understand the need for pgScript, which could be executed using pgAdmin tool. When it should be used? What it can do that plpgSQL cannot do? What is equivalent of it in Microsoft SQL Server?

Answer 1

pgScript is a client-side scripting language, while pl/PgSQL runs on the server. This means they have entirely different use cases. For example, PgScript can manage transaction status while pl/PgSQL cannot, but pl/Pgsql can be used to extend the language of SQL while pgScript cannot do that.

Additionally it means the two will handle many other things quite differently ranging from query plans to dynamic SQL, and while pgScript requires round trips between queries pl/Pgsql does not.

Answer 2

One use for pgScript is to define variables and use them later in your SQLs.

For example, you could do something like this:

declare @mytbl, @maxid; 
set @mytbl = 'sometable'; 
set @maxid = 2500; 
set @res = select count(*) from @mytbl where id <= @maxid;
print @res;

This approach is to just have any variables you want to change at the top of your script, rather than those getting buried deep inside complex SQL queries.

Of course, pgScript is a feature available only inside PgAdmin III client like @{Craig Ringer} mentioned in his comment.