informix_fdw

Overview

PGDG non-free (pgnf) only; no SQL-level extension dependency; runtime requires IBM Informix Client SDK (libifsql15a/libifasf15a/libifgen15a/libifos15a/libifgls)

Version

Install

You can install informix_fdw directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf

pig install informix_fdw;          # Install for current active PG version

pig ext install -y informix_fdw -v 18  # PG 18
pig ext install -y informix_fdw -v 17  # PG 17
pig ext install -y informix_fdw -v 16  # PG 16
pig ext install -y informix_fdw -v 15  # PG 15
pig ext install -y informix_fdw -v 14  # PG 14

dnf install -y informix_fdw_18       # PG 18
dnf install -y informix_fdw_17       # PG 17
dnf install -y informix_fdw_16       # PG 16
dnf install -y informix_fdw_15       # PG 15
dnf install -y informix_fdw_14       # PG 14

Create Extension:

CREATE EXTENSION informix_fdw;

Usage

informix_fdw: Foreign data wrapper for Informix access

Create Server

CREATE EXTENSION informix_fdw;

CREATE SERVER informix_server
  FOREIGN DATA WRAPPER informix_fdw
  OPTIONS (
    informixserver 'informix_hostname',
    informixdir '/opt/informix/csdk'
  );

Server Options: informixserver (required, Informix server identifier from sqlhosts file), informixdir (required, path to Informix Client SDK), disable_predicate_pushdown (disable WHERE pushdown), gl_datetime (custom datetime format, default %iY-%m-%d %H:%M:%S), gl_date (custom date format, default %iY-%m-%d).

Create User Mapping

CREATE USER MAPPING FOR CURRENT_USER
  SERVER informix_server
  OPTIONS (username 'informix', password 'secret');

Create Foreign Table

CREATE FOREIGN TABLE remote_table (
  id bigint NOT NULL,
  name varchar(255),
  amount numeric(10,2),
  created timestamp
)
SERVER informix_server
OPTIONS (
  database 'mydb',
  table 'remote_table',
  client_locale 'en_US.utf8',
  db_locale 'en_US.819'
);

Table Options: database (required, remote database name), table or query (one required), client_locale (required, must match PostgreSQL server encoding), db_locale (required, should match Informix locale), disable_rowid (use updatable cursor instead of ROWID), enable_blobs (include if table contains BLOBs).

Use query instead of table for view-like access:

CREATE FOREIGN TABLE remote_view (
  id bigint,
  total numeric(10,2)
)
SERVER informix_server
OPTIONS (
  database 'mydb',
  query 'SELECT id, SUM(amount) AS total FROM orders GROUP BY id',
  client_locale 'en_US.utf8',
  db_locale 'en_US.819'
);

CRUD Operations

SELECT * FROM remote_table WHERE id > 100;
INSERT INTO remote_table (id, name, amount) VALUES (1, 'test', 99.99);
UPDATE remote_table SET amount = 100.00 WHERE id = 1;
DELETE FROM remote_table WHERE id = 1;

Supported Data Types

Queries: BOOLEAN, DATE, DATETIME, INTERVAL, SMALLINT, INTEGER, BIGINT, SERIAL, VARCHAR, CHARACTER, TEXT, NUMERIC, MONEY.

DML operations: SERIAL, INTEGER, BIGINT, INTERVAL, TEXT, BYTEA, VARCHAR, NVARCHAR, TIMESTAMP, DATE, NUMERIC, MONEY.