SQL Pass-Through Facility Specifics for PostgreSQL

Key Information

For general information about this feature, see SQL Pass-Through Facility.

Here are the SQL pass-through facility specifics for the PostgreSQL interface.

• The dbms-name is POSTGRES.
• The CONNECT statement is required.
• PROC SQL supports multiple connections to PostgreSQL. If you use multiple simultaneous connections, you must use the alias argument to identify the different connections. If you do not specify an alias, the default POSTGRES alias is used.
• The CONNECT statement database-connection-arguments are identical to its LIBNAME connection options.

CONNECT Statement Examples for PostgreSQL

This example connects to PostgreSQL and then disconnects from it.

proc sql noerrorstop;
   connect to postgres as x1(server=mysrv1 port=5432 
      user=mysur1 password='mypwd1' database=mydb1);
disconnect from x1;
quit;

This next example connects to PostgreSQL, executes some SQL statements, and then disconnects from PostgreSQL.

proc sql noerrorstop;
 connect to postgres as x1(server=mysrv1 port=5432 
    user=mysur1 password='mypwd1' database=mydb1);

 execute ( CREATE TABLE t1 ( no int primary key, state varchar(10) ) ) by x1;
 execute ( INSERT INTO t1 values (1, 'USA') ) by x1;
 execute ( INSERT INTO t1 values (2, 'CHN') ) by x1;
 select * from connection to x1 (SELECT * FROM t1 ORDER BY no);

 disconnect from x1;
 quit;