Trying to select from imported table

[xtpclark]

Not sure why this error is happening... I am able to create server, create user mapping, import remote schema, but when I try to select I get the following:

fdw_test=# select * from subject;
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  informix_fdw: get foreign relation size, cmd 1
ERROR:  missing required FDW options (informixserver, informixdir, client_locale, database)

                       Foreign table "public.subject"
   Column    |     Type      | Collation | Nullable | Default | FDW options 
-------------+---------------+-----------+----------+---------+-------------
 subject_num | integer       |           | not null |         | 
 description | character(45) |           |          |         | 
 upperdesc   | character(45) |           |          |         | 
Server: ifx_dev_server
FDW options: ("table" 'subject', client_locale 'en_US.819', db_locale 'en_US.819', database 'dev_db')

Schema definition from pg_dump:

--
-- Name: informix_fdw; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS informix_fdw WITH SCHEMA public;

--
-- Name: EXTENSION informix_fdw; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION informix_fdw IS 'foreign data wrapper for Informix IDS 11 access';

--
-- Name: ifx_dev_server; Type: SERVER; Schema: -; Owner: postgres
--

CREATE SERVER ifx_dev_server FOREIGN DATA WRAPPER informix_fdw OPTIONS (
    client_locale 'en_US.819',
    database 'dev_db',
    db_locale 'en_US.819',
    informixserver 'dev_server'
);

ALTER SERVER ifx_dev_server OWNER TO postgres;

--
-- Name: USER MAPPING postgres SERVER ifx_dev_server; Type: USER MAPPING; Schema: -; Owner: postgres
--

CREATE USER MAPPING FOR postgres SERVER ifx_dev_server OPTIONS (
    password 'somepassword',
    username 'somepguser'
);

--
-- Name: subject; Type: FOREIGN TABLE; Schema: public; Owner: postgres
--

CREATE FOREIGN TABLE public.subject (
    subject_num integer NOT NULL,
    description character(45),
    upperdesc character(45)
)
SERVER ifx_dev_server
OPTIONS (
    client_locale 'en_US.819',
    database 'dev_db',
    db_locale 'en_US.819',
    "table" 'subject'
);

[anse1]

Seems like you are missing the required option informixdir in the CREATE SERVER statement. It should be /path/to/your/csdk/installation, e.g. '/Applications/IBM/informix'

[xtpclark]

Issue was informixdir in CREATE SERVER. Fixed!

Thank you.