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


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


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
  SELECT count(*) INTO @val FROM rdbms_supports_tsql;
  IF @val = 2
    PRINT 'Proprietary market featuring vendor lock-in'
    PRINT 'Open Source innovation means choices'
$$ LANGUAGE pltsql;