Sqlite_fdw usage

Postgresql provides the function of foreign key table. The only regret of sqlite_fdw is that it can only be select. I hope it can be updated on github as soon as possible.

Now I will introduce how to use sqlite_fdw!

1. Download the sqlite_fdw package:

Git clone https://github.com/gleu/sqlite_fdw or download .ZIP zip file

Make

[postgres@mxl sqlite_fdw] $make

Gcc-Wall-Wmissing-prototypes-Wpointer-arith-Wdeclaration-after-statement-Wendif-labels-Wmissing-format-attribute-Wformat-security-fno-strict-aliasing-fwrapv-O2-fpic-I. -I. /-I/usr/local/pgsql/include/server-I/usr/local/pgsql/include/internal-D_GNU_SOURCE-I/usr/include/libxml2-c-o src/sqlite_fdw.o src/sqlite_fdw.c

Gcc-Wall-Wmissing-prototypes-Wpointer-arith-Wdeclaration-after-statement-Wendif-labels-Wmissing-format-attribute-Wformat-security-fno-strict-aliasing-fwrapv-O2-fpic-shared-o sqlite_fdw.so src/sqlite_fdw.o-L/usr/local/pgsql/lib-Wl,--as-needed-Wl,-rpath,'/usr/local/pgsql/lib',--enable-new-dtags-lsqlite3

Make install

[root@mxl sqlite_fdw] # make install

/ bin/mkdir-p'/ usr/local/pgsql/lib'

/ bin/mkdir-p'/ usr/local/pgsql/share/extension'

/ bin/mkdir-p'/ usr/local/pgsql/share/extension'

/ bin/mkdir-p'/ usr/local/pgsql/share/doc/extension'

/ usr/bin/install-c-m 755 sqlite_fdw.so' / usr/local/pgsql/lib/sqlite_fdw.so'

/ usr/bin/install-c-m 644 sqlite_fdw.control'/ usr/local/pgsql/share/extension/'

/ usr/bin/install-c-m 644 sql/sqlite_fdw--0.0.1.sql'/ usr/local/pgsql/share/extension/'

/ usr/bin/install-c-m 644 doc/sqlite_fdw.md'/ usr/local/pgsql/share/doc/extension/'

2. Reference sqlite_fdw in the database

Postgres=# CREATE EXTENSION sqlite_fdw

CREATE EXTENSION

Postgres=#\ dx

List of installed extensions

Name | Version | Schema | Description

-+-

Pg_stat_statements | 1.2 | public | track execution statistics of all SQL statements executed

Plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

Sqlite_fdw | 0.0.1 | public | SQLite Foreign Data Wrapper

Uuid-ossp | 1.0 | public | generate universally unique identifiers (UUIDs)

(4 rows)

3. Create a server of fdw

CREATE SERVER sqlite_server FOREIGN DATA WRAPPER sqlite_fdw OPTIONS (database'/ tmp/localdb.sqlite')

4. Create a foreign key table

CREATE FOREIGN TABLE fdwuserrole (userid integer,roleid ingeter) SERVER sqlite_server OPTIONS (table 'userrole')

5. View the foreign key table server

Postgres=#\ des

List of foreign servers

Name | Owner | Foreign-data wrapper

-+-

Sqlite_server | postgres | sqlite_fdw

(1 row)

6. Delete foreign key table

Drop FOREIGN table fdwuserrole

Question: 1

Src/sqlite_fdw.c:37:21: error: sqlite3.h: No such file or directory

Make prompt that the sqlite header file cannot be parsed

Rpm-Uvh http://www6.atomicorp.com/channels/atomic/centos/6/x86_64/RPMS/atomic-release-1.0-21.el6.art.noarch.rpm

Yum update

Yum sqlite-devel install-y

Question: 2

You need to use postgres's pg_config when installing sqlite_fdw, so you need to add the environment variable of postgres to root.