pgTSQL Stored Procedures

T-SQL (Transact-SQL) is the procedural programming language built into Sybase & SQL Server. It's functionally the equivalent of PostgreSQL's PL/pgSQL. It looks a bit odd because it has unique conventions like:

  • Semicolons are not required at the end of each line of code
     
  • Variable names always are prefixed with @ signs
     
  • IF statements do not need to be closed by END IF's
     
  • Temporary tables are automagic if the table name has a # prefix

Installation

First lets use the pgc commandline to install PostgreSQL 10 and then the pgtsql extension into the 'demo' database.

./pgc install pg10
./pgc start pg10 -d demo
./pgc install pgtsql -d demo

Example

Now lets use psql to create a table and a small sample TSQL function as follows.



CREATE TABLE rdbms_supports_tsql (
  organization  varchar(10) primary key
);
INSERT INTO rdbms_supports_tsql VALUES ('SYBASE');
INSERT INTO rdbms_supports_tsql VALUES ('SQL-SERVER');
INSERT INTO rdbms_supports_tsql VALUES ('POSTGRES');


CREATE OR REPLACE FUNCTION query_tsql_rdbms() RETURNS void AS $$
  DECLARE @val int = 0
BEGIN
  SELECT count(*) INTO @val FROM rdbms_supports_tsql;
  IF @val = 2
    PRINT 'Proprietary market featuring vendor lock-in'
  ELSE
    PRINT 'Open Source innovation means choices'
END
$$ LANGUAGE pltsql;