[Git][debian-gis-team/pgsql-ogr-fdw][experimental] 62 commits: Revert "Update branch in gbp.conf & Vcs-Git URL."
Bas Couwenberg (@sebastic)
gitlab at salsa.debian.org
Wed May 21 04:30:12 BST 2025
Bas Couwenberg pushed to branch experimental at Debian GIS Project / pgsql-ogr-fdw
Commits:
2e58661c by Bas Couwenberg at 2021-08-15T16:38:10+02:00
Revert "Update branch in gbp.conf & Vcs-Git URL."
This reverts commit 95811d2baf2a87f2f6dd96510391ce6771e3dc50.
- - - - -
4e96c152 by Bas Couwenberg at 2021-08-15T16:38:23+02:00
Move from experimental to unstable.
- - - - -
729a242a by Bas Couwenberg at 2021-09-08T17:37:39+02:00
Bump Standards-Version to 4.6.0, no changes.
- - - - -
81d0cebc by Bas Couwenberg at 2021-09-13T17:03:21+02:00
Bump debhelper compat to 12, no changes.
- - - - -
2e9a668f by Bas Couwenberg at 2021-10-05T21:44:06+02:00
Update packaging for postgresql-14.
- - - - -
2a72e434 by Bas Couwenberg at 2021-10-05T21:48:53+02:00
Set distribution to experimental.
- - - - -
99d1f50e by Bas Couwenberg at 2021-10-08T14:23:25+02:00
Move from experimental to unstable.
- - - - -
3e3ea19d by Bas Couwenberg at 2021-10-21T20:43:30+02:00
Add patch to fix pgsql test failure.
- - - - -
d47ae77e by Bas Couwenberg at 2021-10-21T20:45:34+02:00
Set distribution to unstable.
- - - - -
f2d54fc7 by Bas Couwenberg at 2021-10-23T19:21:27+02:00
Update watch file to use tags instead of releases.
- - - - -
8c52be85 by Bas Couwenberg at 2022-06-21T07:16:38+02:00
Bump Standards-Version to 4.6.1, no changes.
- - - - -
2aea16b6 by Christoph Berg at 2022-07-29T21:56:57+02:00
Update upstream source from tag 'upstream/1.1.2'
Update to upstream version '1.1.2'
with Debian dir 411d290d77859cd09b8228bbfc682f697e555d03
- - - - -
2428c85d by Christoph Berg at 2022-07-29T21:56:57+02:00
New upstream version 1.1.2
- - - - -
9e17f934 by Christoph Berg at 2022-07-29T22:03:11+02:00
New upstream version 1.1.2.
- - - - -
99935553 by Christoph Berg at 2022-07-29T22:12:44+02:00
releasing package pgsql-ogr-fdw version 1.1.2-1
- - - - -
827eb003 by Bas Couwenberg at 2022-10-15T07:41:16+02:00
Update packaging for postgresql-15.
- - - - -
84fcbdda by Bas Couwenberg at 2022-10-15T07:41:38+02:00
Set distribution to experimental.
- - - - -
c1cf9049 by Bas Couwenberg at 2022-10-15T22:02:52+02:00
Move from experimental to unstable.
- - - - -
60a22343 by Bas Couwenberg at 2022-10-17T06:52:33+02:00
Enable Salsa CI.
- - - - -
5545a780 by Bas Couwenberg at 2022-10-18T05:20:31+02:00
New upstream version 1.1.3
- - - - -
099867a1 by Bas Couwenberg at 2022-10-18T05:20:34+02:00
Update upstream source from tag 'upstream/1.1.3'
Update to upstream version '1.1.3'
with Debian dir 89b9e65f0f76a8b817615559f00d2c503d9e9d30
- - - - -
54f1a3a1 by Bas Couwenberg at 2022-10-18T05:20:46+02:00
New upstream release.
- - - - -
a0be4dfc by Bas Couwenberg at 2022-10-18T05:21:53+02:00
Refresh patches.
- - - - -
0812834b by Bas Couwenberg at 2022-10-18T05:22:08+02:00
Set distribution to unstable.
- - - - -
a2680963 by Bas Couwenberg at 2022-11-28T19:48:05+01:00
Add Rules-Requires-Root to control file.
- - - - -
d22e6ca0 by Bas Couwenberg at 2023-01-18T17:06:03+01:00
Bump Standards-Version to 4.6.2, no changes.
- - - - -
449a3a00 by Bas Couwenberg at 2023-06-12T21:01:49+02:00
Bump debhelper compat to 13.
- - - - -
03109975 by Bas Couwenberg at 2023-07-19T05:21:33+02:00
New upstream version 1.1.4
- - - - -
e9e2c6fc by Bas Couwenberg at 2023-07-19T05:21:35+02:00
Update upstream source from tag 'upstream/1.1.4'
Update to upstream version '1.1.4'
with Debian dir 08a7d91006d19c8c2c72b235f1883207c09d33ea
- - - - -
25426b16 by Bas Couwenberg at 2023-07-19T05:27:48+02:00
New upstream release.
- - - - -
a5d3f5a3 by Bas Couwenberg at 2023-07-19T05:28:48+02:00
Revert postgresql-13 changes.
- - - - -
d4f6c65c by Bas Couwenberg at 2023-07-19T05:29:37+02:00
Set distribution to unstable.
- - - - -
adb85e1c by Bas Couwenberg at 2023-08-13T09:07:07+02:00
Reorder rules.
- - - - -
9f7dde9c by Bas Couwenberg at 2023-08-25T17:28:24+02:00
Explicitly enable test-build-twice job.
- - - - -
74b01918 by Christoph Berg at 2023-09-04T12:38:26+02:00
Remove newpid from test dependencies.
- - - - -
d6bd834b by Bas Couwenberg at 2023-09-16T18:36:28+02:00
Update packaging for postgresql-16.
- - - - -
0917d0e1 by Bas Couwenberg at 2023-09-16T18:37:12+02:00
Set distribution to experimental.
- - - - -
1b5d9b4e by Bas Couwenberg at 2023-09-17T11:17:49+02:00
Move from experimental to unstable.
- - - - -
4790b954 by Bas Couwenberg at 2023-09-17T11:40:51+02:00
Add ${postgresql:Depends} to Depends.
- - - - -
dbd16d92 by Bas Couwenberg at 2024-06-28T06:12:57+02:00
Update upstream source from tag 'upstream/1.1.5'
Update to upstream version '1.1.5'
with Debian dir 0270c7aea1844ad1e8e9618c42ab129007f9cd5b
- - - - -
669ced5e by Bas Couwenberg at 2024-06-28T06:12:57+02:00
New upstream version 1.1.5
- - - - -
10955312 by Bas Couwenberg at 2024-06-28T06:13:15+02:00
New upstream release.
- - - - -
305aa693 by Bas Couwenberg at 2024-06-28T06:13:51+02:00
Refresh patches.
- - - - -
06e5e2b4 by Bas Couwenberg at 2024-06-28T06:14:00+02:00
Set distribution to unstable.
- - - - -
bc58cd67 by Christoph Berg at 2024-07-15T10:33:47+00:00
Bump supported PG versions to 11
- - - - -
f9213f3a by Bas Couwenberg at 2024-07-28T19:50:10+02:00
Bump Standards-Version to 4.7.0, no changes.
- - - - -
21cd96f7 by Bas Couwenberg at 2024-09-11T11:02:33+02:00
Update packaging for postgresql-17.
- - - - -
5f27ae93 by Bas Couwenberg at 2024-09-11T11:02:51+02:00
Set distribution to experimental.
- - - - -
722bfb84 by Bas Couwenberg at 2024-09-11T13:03:29+02:00
Drop support for 32-bit architectures.
- - - - -
4caa95e8 by Bas Couwenberg at 2024-09-11T13:31:14+02:00
Disable i386 build in Salsa CI.
- - - - -
5fa6428a by Bas Couwenberg at 2024-09-11T20:01:53+02:00
Set distribution to unstable.
- - - - -
0e370067 by Bas Couwenberg at 2025-03-11T05:27:50+01:00
New upstream version 1.1.6
- - - - -
76366b1a by Bas Couwenberg at 2025-03-11T05:27:51+01:00
Update upstream source from tag 'upstream/1.1.6'
Update to upstream version '1.1.6'
with Debian dir 4dbe3dbd27c5547a2c7fee8cdac46fb74b9775e5
- - - - -
e220e484 by Bas Couwenberg at 2025-03-11T05:28:19+01:00
New upstream release.
- - - - -
5664cf34 by Bas Couwenberg at 2025-03-11T05:29:17+01:00
Set distribution to unstable.
- - - - -
f727d5ad by Bas Couwenberg at 2025-03-20T06:15:47+01:00
Bump Standards-Version to 4.7.2, no changes.
- - - - -
490ea850 by Bas Couwenberg at 2025-05-21T05:19:47+02:00
Update branch in gbp.conf & Vcs-Git URL.
- - - - -
d361f4cc by Bas Couwenberg at 2025-05-21T05:19:57+02:00
New upstream version 1.1.7
- - - - -
49b13630 by Bas Couwenberg at 2025-05-21T05:19:58+02:00
Update upstream source from tag 'upstream/1.1.7'
Update to upstream version '1.1.7'
with Debian dir 6924cd549986f46e445e2b162e66e1a073d93ce3
- - - - -
126d8fdf by Bas Couwenberg at 2025-05-21T05:20:12+02:00
New upstream release.
- - - - -
333f6f10 by Bas Couwenberg at 2025-05-21T05:21:19+02:00
Refresh patches.
- - - - -
695e6e7f by Bas Couwenberg at 2025-05-21T05:21:50+02:00
Set distribution to experimental.
- - - - -
28 changed files:
- + .github/workflows/ci.yml
- − .travis.yml
- Makefile
- README.md
- data/no_geom_apost.csv
- data/pt_two.prj
- + debian/.gitlab-ci.yml
- debian/changelog
- − debian/compat
- debian/control
- debian/control.in
- debian/patches/hardingflags.patch
- debian/patches/install.patch
- debian/pgversions
- debian/rules
- debian/tests/control
- debian/tests/installcheck
- debian/watch
- input/import.source
- input/pgsql.source
- ogr_fdw.c
- ogr_fdw.h
- ogr_fdw_common.c
- ogr_fdw_func.c
- ogr_fdw_info.c
- output/import.source
- output/pgsql.source
- stringbuffer.c
Changes:
=====================================
.github/workflows/ci.yml
=====================================
@@ -0,0 +1,74 @@
+# GitHub Actions for OGR-FDW
+#
+# Paul Ramsey <pramsey at cleverelephant dot ca>
+
+name: "CI"
+on:
+ push:
+# branches-ignore:
+# - 'master'
+ pull_request: ~
+
+jobs:
+ linux:
+ name: "CI"
+ strategy:
+ fail-fast: false
+ matrix:
+ ci:
+ - { PGVER: 11 }
+ - { PGVER: 12 }
+ - { PGVER: 13 }
+ - { PGVER: 14 }
+ - { PGVER: 15 }
+ - { PGVER: 16 }
+ - { PGVER: 17 }
+
+ runs-on: ubuntu-latest
+ steps:
+
+ - name: 'Check Out'
+ uses: actions/checkout at v4
+
+ - name: 'Raise Priority for apt.postgresql.org'
+ run: |
+ cat << EOF >> ./pgdg.pref
+ Package: *
+ Pin: release o=apt.postgresql.org
+ Pin-Priority: 600
+ EOF
+ sudo mv ./pgdg.pref /etc/apt/preferences.d/
+ sudo apt update
+
+ - name: 'Install GDAL'
+ run: |
+ sudo apt-get install libgdal-dev
+
+ - name: 'Install PostgreSQL'
+ run: |
+ sudo apt-get purge postgresql-*
+ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg-snapshot main ${{ matrix.ci.PGVER }}" > /etc/apt/sources.list.d/pgdg.list'
+ curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg >/dev/null
+ sudo apt-get update
+ sudo apt-get -y install postgresql-${{ matrix.ci.PGVER }} postgresql-server-dev-${{ matrix.ci.PGVER }}
+
+ - name: 'Start PostgreSQL'
+ run: |
+ export PGVER=${{ matrix.ci.PGVER }}
+ export PGDATA=/var/lib/postgresql/$PGVER/main
+ export PGETC=/etc/postgresql/$PGVER/main
+ export PGBIN=/usr/lib/postgresql/$PGVER/bin
+ export RUNNER_USER=`whoami`
+ # make sure postgres user can access data files
+ sudo chmod -R 755 /home/${RUNNER_USER}
+ sudo cp ./ci/pg_hba.conf $PGETC/pg_hba.conf
+ sudo systemctl stop postgresql
+ sudo pg_ctlcluster $PGVER main start
+ sudo pg_lsclusters
+
+ - name: 'Build & Test'
+ run: |
+ export PATH=/usr/lib/postgresql/${{ matrix.ci.PGVER }}/bin/:$PATH
+ PG_CFLAGS=-Werror make
+ sudo make install
+ PGUSER=postgres make installcheck || (cat regression.diffs && /bin/false)
=====================================
.travis.yml deleted
=====================================
@@ -1,52 +0,0 @@
-dist: xenial
-sudo: required
-language: cpp
-compiler: gcc
-
-addons:
- apt:
- packages:
- - curl
- - libcurl4-gnutls-dev
- - ca-certificates
- - software-properties-common
- - python-software-properties
- - gnupg-curl
-
-env:
- - PG_VERSION=9.6 GDAL_VERSION=1.11
- - PG_VERSION=9.6 GDAL_VERSION=2.2.4
- - PG_VERSION=10 GDAL_VERSION=2.3.3
- - PG_VERSION=11 GDAL_VERSION=2.4.4
- - PG_VERSION=12 GDAL_VERSION=2.4.4
-
-before_script:
- - sudo /etc/init.d/postgresql stop
- - sudo apt-get -y --purge remove postgresql-9.1
- - sudo apt-get -y --purge remove postgresql-9.2
- - sudo apt-get -y --purge remove postgresql-9.3
- - sudo apt-get -y --purge remove postgresql-9.4
- - sudo apt-get -y --purge remove postgresql-9.5
- - sudo apt-get -y --purge remove postgresql-9.6
- - sudo apt-get -y --purge remove postgresql-10
- - sudo apt-get -y --purge remove postgresql-11
- - sudo apt-get -y --purge remove postgresql-common
- - sudo apt-get -y autoremove
- - sudo rm -rf /var/lib/postgresql
- - apt-key adv --fetch-keys https://www.postgresql.org/media/keys/ACCC4CF8.asc
- - sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main $PG_VERSION"
- - sudo apt-get update -qq
- - sudo apt-get -y install postgresql-$PG_VERSION postgresql-server-dev-$PG_VERSION
- - sudo cp ci/pg_hba.conf /etc/postgresql/$PG_VERSION/main/pg_hba.conf
- - sudo /etc/init.d/postgresql reload
-
- - if [[ $GDAL_VERSION == 1* ]]; then sudo apt-get install libgdal1i libgdal-dev; fi
- - if [[ $GDAL_VERSION == 2* ]]; then bash ci/gdal_build.sh $GDAL_VERSION; fi
- - if [[ $GDAL_VERSION == 3* ]]; then bash ci/gdal_build.sh $GDAL_VERSION; fi
- # needs proj6
-
-
-script:
- - make && sudo make install && sudo chmod 755 $HOME && (PGUSER=postgres make installcheck || (cat regression.diffs && /bin/false))
-
-
=====================================
Makefile
=====================================
@@ -20,7 +20,10 @@ EXTRA_CLEAN = sql/*.sql expected/*.out
GDAL_CONFIG = gdal-config
GDAL_CFLAGS = $(shell $(GDAL_CONFIG) --cflags)
-GDAL_LIBS = $(shell $(GDAL_CONFIG) --libs)
+GDAL_LIBS = $(shell $(GDAL_CONFIG) --libs)
+
+# For MacOS
+# GDAL_LIBS += -rpath $(shell $(GDAL_CONFIG) --prefix)/lib
PG_CONFIG = pg_config
REGRESS_OPTS = --encoding=UTF8
@@ -32,15 +35,36 @@ SHLIB_LINK := $(LIBS)
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
-PG_VERSION_NUM = $(shell awk '/PG_VERSION_NUM/ { print $$3 }' $(shell $(PG_CONFIG) --includedir-server)/pg_config.h)
+PG_VERSION_NUM = $(shell pg_config --version | cut -f2 -d' ' | awk -F. '{printf "%d%04d", $$1, $$2}')
HAS_IMPORT_SCHEMA = $(shell [ $(PG_VERSION_NUM) -ge 90500 ] && echo yes)
+
# order matters, file first, import last
REGRESS = file pgsql
ifeq ($(HAS_IMPORT_SCHEMA),yes)
REGRESS += import
endif
+# work around pg15 change to regression file variable
+# substitution for @abs_srcdir@ until we can drop older
+# version support
+# https://github.com/postgres/postgres/commit/d1029bb5a26cb84b116b0dee4dde312291359f2a
+PG15 := $(shell [ $(PG_VERSION_NUM) -ge 150000 ] && echo yes)
+ifeq ($(PG15),yes)
+
+sql/%.sql: input/%.source
+ perl -pe 's#\@abs_srcdir\@#$(PWD)#g' < $< > $@
+
+expected/%.out: output/%.source
+ perl -pe 's#\@abs_srcdir\@#$(PWD)#g' < $< > $@
+
+SQLFILES := sql/file.sql sql/import.sql sql/pgsql.sql sql/postgis.sql
+OUTFILES := expected/file.out expected/import.out expected/pgsql.out expected/postgis.out
+
+installcheck: $(SQLFILES) $(OUTFILES)
+
+endif
+
###############################################################
# Build the utility program after PGXS to override the
# PGXS environment
=====================================
README.md
=====================================
@@ -1,4 +1,4 @@
-Travis: [](http://travis-ci.org/pramsey/pgsql-ogr-fdw)
+[](https://github.com/pramsey/pgsql-ogr-fdw/actions/workflows/ci.yml)
# PostgreSQL OGR Foreign Data Wrapper
@@ -10,7 +10,7 @@ OGR is the **vector** half of the [GDAL](http://www.gdal.org/) spatial data acce
This implementation currently has the following limitations:
-* **PostgreSQL 9.3 or higher.** This wrapper does not support the FDW implementations in older versions of PostgreSQL.
+* **PostgreSQL 11 or higher.**
* **Limited non-spatial query restrictions are pushed down to OGR.** OGR only supports a [minimal set](https://gdal.org/user/ogr_sql_dialect.html) of SQL operators (>, <, <=, >=, =).
* **Only bounding box filters (&&) are pushed down.** Spatial filtering is possible, but only bounding boxes, and only using the && operator.
* **OGR connections every time** Rather than pooling OGR connections, each query makes (and disposes of) **two** new ones, which seems to be the largest performance drag at the moment for restricted (small) queries.
@@ -18,11 +18,11 @@ This implementation currently has the following limitations:
## Download
* Windows
- * Via [Stackbuilder](https://www.postgresql.org/download/windows/) (part of PostGIS Bundle)
+ * Via [Stackbuilder](https://www.postgresql.org/download/windows/) (part of PostGIS Bundle)
* Linux
* [Arch Linux](https://aur.archlinux.org/packages/pgsql-ogr-fdw/)
- * [Ubuntu](https://launchpad.net/ubuntu/+source/pgsql-ogr-fdw)
- * [Red Hat](https://yum.postgresql.org/)
+ * [Ubuntu](https://launchpad.net/ubuntu/+source/pgsql-ogr-fdw), [PGDG Apt: Debian / Ubuntu](https://apt.postgresql.org)
+ * [PGDG Yum: Redhat EL, CentOS, Rocky](https://yum.postgresql.org/)
* OSX
## Basic Operation
@@ -46,39 +46,39 @@ Use the `ogr_fdw_info` tool to read an OGR data source and output a server and t
# ogr_fdw_info -f
Supported Formats:
- -> "PCIDSK" (read/write)
- -> "netCDF" (read/write)
- ...
- -> "HTTP" (readonly)
+ -> "PCIDSK" (read/write)
+ -> "netCDF" (read/write)
+ ...
+ -> "HTTP" (readonly)
# ogr_fdw_info -s /tmp/test
Layers:
- pt_two
+ pt_two
# ogr_fdw_info -s /tmp/test -l pt_two
CREATE SERVER "myserver"
- FOREIGN DATA WRAPPER ogr_fdw
- OPTIONS (
- datasource '/tmp/test',
- format 'ESRI Shapefile' );
+ FOREIGN DATA WRAPPER ogr_fdw
+ OPTIONS (
+ datasource '/tmp/test',
+ format 'ESRI Shapefile' );
CREATE FOREIGN TABLE "pt_two" (
- fid integer,
- "geom" geometry(Point, 4326),
- "name" varchar,
- "age" integer,
- "height" real,
- "birthdate" date )
- SERVER "myserver"
- OPTIONS (layer 'pt_two');
+ fid integer,
+ "geom" geometry(Point, 4326),
+ "name" varchar,
+ "age" integer,
+ "height" real,
+ "birthdate" date )
+ SERVER "myserver"
+ OPTIONS (layer 'pt_two');
```
Copy the `CREATE SERVER` and `CREATE FOREIGN SERVER` SQL commands into the database and you'll have your foreign table definition.
```
- Foreign table "public.pt_two"
- Column | Type | Modifiers | FDW Options
+ Foreign table "public.pt_two"
+ Column | Type | Modifiers | FDW Options
----------+-------------------+-----------+-------------
fid | integer | |
geom | geometry | |
@@ -98,8 +98,8 @@ SELECT * FROM pt_two;
```
fid | geom | name | age | height | birthday
-----+--------------------------------------------+-------+-----+--------+------------
- 0 | 0101000000C00497D1162CB93F8CBAEF08A080E63F | Peter | 45 | 5.6 | 1965-04-12
- 1 | 010100000054E943ACD697E2BFC0895EE54A46CF3F | Paul | 33 | 5.84 | 1971-03-25
+ 0 | 0101000000C00497D1162CB93F8CBAEF08A080E63F | Peter | 45 | 5.6 | 1965-04-12
+ 1 | 010100000054E943ACD697E2BFC0895EE54A46CF3F | Paul | 33 | 5.84 | 1971-03-25
```
You can also apply filters, and see the portions that will be pushed down to the OGR driver, by setting the debug level to `DEBUG1`.
@@ -132,40 +132,40 @@ CREATE EXTENSION postgis;
CREATE EXTENSION ogr_fdw;
CREATE SERVER geoserver
- FOREIGN DATA WRAPPER ogr_fdw
- OPTIONS (
- datasource 'WFS:https://demo.geo-solutions.it/geoserver/wfs',
- format 'WFS' );
+ FOREIGN DATA WRAPPER ogr_fdw
+ OPTIONS (
+ datasource 'WFS:https://demo.geo-solutions.it/geoserver/wfs',
+ format 'WFS' );
CREATE FOREIGN TABLE topp_states (
- fid bigint,
- the_geom Geometry(MultiSurface,4326),
- gml_id varchar,
- state_name varchar,
- state_fips varchar,
- sub_region varchar,
- state_abbr varchar,
- land_km double precision,
- water_km double precision,
- persons double precision,
- families double precision,
- houshold double precision,
- male double precision,
- female double precision,
- workers double precision,
- drvalone double precision,
- carpool double precision,
- pubtrans double precision,
- employed double precision,
- unemploy double precision,
- service double precision,
- manual double precision,
- p_male double precision,
- p_female double precision,
- samp_pop double precision
- )
- SERVER "geoserver"
- OPTIONS (layer 'topp:states');
+ fid bigint,
+ the_geom Geometry(MultiSurface,4326),
+ gml_id varchar,
+ state_name varchar,
+ state_fips varchar,
+ sub_region varchar,
+ state_abbr varchar,
+ land_km double precision,
+ water_km double precision,
+ persons double precision,
+ families double precision,
+ houshold double precision,
+ male double precision,
+ female double precision,
+ workers double precision,
+ drvalone double precision,
+ carpool double precision,
+ pubtrans double precision,
+ employed double precision,
+ unemploy double precision,
+ service double precision,
+ manual double precision,
+ p_male double precision,
+ p_female double precision,
+ samp_pop double precision
+ )
+ SERVER "geoserver"
+ OPTIONS (layer 'topp:states');
```
### FGDB FDW
@@ -173,27 +173,27 @@ CREATE FOREIGN TABLE topp_states (
Unzip the `Querying.zip` file from the `data` directory to get a `Querying.gdb` file, and put it somewhere public (like `/tmp`). Now run the `ogr_fdw_info` tool on it to get a table definition.
```sql
CREATE SERVER fgdbtest
- FOREIGN DATA WRAPPER ogr_fdw
- OPTIONS (
- datasource '/tmp/Querying.gdb',
- format 'OpenFileGDB' );
+ FOREIGN DATA WRAPPER ogr_fdw
+ OPTIONS (
+ datasource '/tmp/Querying.gdb',
+ format 'OpenFileGDB' );
CREATE FOREIGN TABLE cities (
- fid integer,
- geom geometry(Point, 4326),
- city_fips varchar,
- city_name varchar,
- state_fips varchar,
- state_name varchar,
- state_city varchar,
- type varchar,
- capital varchar,
- elevation integer,
- pop1990 integer,
- popcat integer
- )
- SERVER fgdbtest
- OPTIONS (layer 'Cities');
+ fid integer,
+ geom geometry(Point, 4326),
+ city_fips varchar,
+ city_name varchar,
+ state_fips varchar,
+ state_name varchar,
+ state_city varchar,
+ type varchar,
+ capital varchar,
+ elevation integer,
+ pop1990 integer,
+ popcat integer
+ )
+ SERVER fgdbtest
+ OPTIONS (layer 'Cities');
```
Query away!
@@ -204,48 +204,48 @@ This is only for testing, for best performance you should use postgres_fdw forei
```sql
CREATE TABLE apostles (
- fid serial primary key,
- geom geometry(Point, 4326),
- joined integer,
- name text,
- height real,
- born date,
- clock time,
- ts timestamp
+ fid serial primary key,
+ geom geometry(Point, 4326),
+ joined integer,
+ name text,
+ height real,
+ born date,
+ clock time,
+ ts timestamp
);
INSERT INTO apostles (name, geom, joined, height, born, clock, ts) VALUES
- ('Peter', 'SRID=4326;POINT(30.31 59.93)', 1, 1.6, '1912-01-10', '10:10:01', '1912-01-10 10:10:01'),
- ('Andrew', 'SRID=4326;POINT(-2.8 56.34)', 2, 1.8, '1911-02-11', '10:10:02', '1911-02-11 10:10:02'),
- ('James', 'SRID=4326;POINT(-79.23 42.1)', 3, 1.72, '1910-03-12', '10:10:03', '1910-03-12 10:10:03'),
- ('John', 'SRID=4326;POINT(13.2 47.35)', 4, 1.45, '1909-04-01', '10:10:04', '1909-04-01 10:10:04'),
- ('Philip', 'SRID=4326;POINT(-75.19 40.69)', 5, 1.65, '1908-05-02', '10:10:05', '1908-05-02 10:10:05'),
- ('Bartholomew', 'SRID=4326;POINT(-62 18)', 6, 1.69, '1907-06-03', '10:10:06', '1907-06-03 10:10:06'),
- ('Thomas', 'SRID=4326;POINT(-80.08 35.88)', 7, 1.68, '1906-07-04', '10:10:07', '1906-07-04 10:10:07'),
- ('Matthew', 'SRID=4326;POINT(-73.67 20.94)', 8, 1.65, '1905-08-05', '10:10:08', '1905-08-05 10:10:08'),
- ('James Alpheus', 'SRID=4326;POINT(-84.29 34.07)', 9, 1.78, '1904-09-06', '10:10:09', '1904-09-06 10:10:09'),
- ('Thaddaeus', 'SRID=4326;POINT(79.13 10.78)', 10, 1.88, '1903-10-07', '10:10:10', '1903-10-07 10:10:10'),
- ('Simon', 'SRID=4326;POINT(-85.97 41.75)', 11, 1.61, '1902-11-08', '10:10:11', '1902-11-08 10:10:11'),
- ('Judas Iscariot', 'SRID=4326;POINT(35.7 32.4)', 12, 1.71, '1901-12-09', '10:10:12', '1901-12-09 10:10:12');
+ ('Peter', 'SRID=4326;POINT(30.31 59.93)', 1, 1.6, '1912-01-10', '10:10:01', '1912-01-10 10:10:01'),
+ ('Andrew', 'SRID=4326;POINT(-2.8 56.34)', 2, 1.8, '1911-02-11', '10:10:02', '1911-02-11 10:10:02'),
+ ('James', 'SRID=4326;POINT(-79.23 42.1)', 3, 1.72, '1910-03-12', '10:10:03', '1910-03-12 10:10:03'),
+ ('John', 'SRID=4326;POINT(13.2 47.35)', 4, 1.45, '1909-04-01', '10:10:04', '1909-04-01 10:10:04'),
+ ('Philip', 'SRID=4326;POINT(-75.19 40.69)', 5, 1.65, '1908-05-02', '10:10:05', '1908-05-02 10:10:05'),
+ ('Bartholomew', 'SRID=4326;POINT(-62 18)', 6, 1.69, '1907-06-03', '10:10:06', '1907-06-03 10:10:06'),
+ ('Thomas', 'SRID=4326;POINT(-80.08 35.88)', 7, 1.68, '1906-07-04', '10:10:07', '1906-07-04 10:10:07'),
+ ('Matthew', 'SRID=4326;POINT(-73.67 20.94)', 8, 1.65, '1905-08-05', '10:10:08', '1905-08-05 10:10:08'),
+ ('James Alpheus', 'SRID=4326;POINT(-84.29 34.07)', 9, 1.78, '1904-09-06', '10:10:09', '1904-09-06 10:10:09'),
+ ('Thaddaeus', 'SRID=4326;POINT(79.13 10.78)', 10, 1.88, '1903-10-07', '10:10:10', '1903-10-07 10:10:10'),
+ ('Simon', 'SRID=4326;POINT(-85.97 41.75)', 11, 1.61, '1902-11-08', '10:10:11', '1902-11-08 10:10:11'),
+ ('Judas Iscariot', 'SRID=4326;POINT(35.7 32.4)', 12, 1.71, '1901-12-09', '10:10:12', '1901-12-09 10:10:12');
CREATE SERVER wraparound
- FOREIGN DATA WRAPPER ogr_fdw
- OPTIONS (
- datasource 'Pg:dbname=fdw user=postgres',
- format 'PostgreSQL' );
+ FOREIGN DATA WRAPPER ogr_fdw
+ OPTIONS (
+ datasource 'Pg:dbname=fdw user=postgres',
+ format 'PostgreSQL' );
CREATE FOREIGN TABLE apostles_fdw (
- fid integer,
- geom geometry(Point, 4326),
- joined integer,
- name text,
- height real,
- born date,
- clock time,
- ts timestamp
+ fid integer,
+ geom geometry(Point, 4326),
+ joined integer,
+ name text,
+ height real,
+ born date,
+ clock time,
+ ts timestamp
)
SERVER wraparound
- OPTIONS (layer 'apostles');
+ OPTIONS (layer 'apostles');
SELECT * FROM apostles_fdw;
```
@@ -261,10 +261,10 @@ For file-backed drivers, the user under which `postgres` runs will need read/wri
By default, servers and tables are updateable if the OGR driver supports it, but you can turn off updateability at a server or table level using the `updateable` option:
```sql
ALTER SERVER myserver
- OPTIONS (ADD updateable 'false');
+ OPTIONS (ADD updateable 'false');
ALTER FOREIGN TABLE mytable
- OPTIONS (ADD updateable 'false');
+ OPTIONS (ADD updateable 'false');
```
Writeable tables only work if you have included a `fid` column in your table definition. By default, tables imported by `IMPORT FOREIGN SCHEMA` or using the example SQL code from `ogr_fdw_info` include a `fid` column.
@@ -273,21 +273,21 @@ Writeable tables only work if you have included a `fid` column in your table def
You can create an FDW table with any subset of columns from the OGR source you like, just by using the same column names as the source:
```sql
CREATE FOREIGN TABLE typetest_fdw_partial (
- clock time,
- name varchar
- )
- SERVER wraparound
- OPTIONS (layer 'typetest');
+ clock time,
+ name varchar
+ )
+ SERVER wraparound
+ OPTIONS (layer 'typetest');
```
You can also explicitly map remote column names to different local names using the `column_name` option:
```sql
CREATE FOREIGN TABLE typetest_fdw_mapped (
- fid bigint,
- supertime time OPTIONS (column_name 'clock'),
- thebestnamething varchar OPTIONS (column_name 'name')
- )
- SERVER wraparound
- OPTIONS (layer 'typetest');
+ fid bigint,
+ supertime time OPTIONS (column_name 'clock'),
+ thebestnamething varchar OPTIONS (column_name 'name')
+ )
+ SERVER wraparound
+ OPTIONS (layer 'typetest');
```
### Automatic Foreign Table Creation
@@ -303,8 +303,8 @@ If you want to import all tables in the OGR data source use the special schema c
CREATE SCHEMA fgdball;
IMPORT FOREIGN SCHEMA ogr_all
- FROM SERVER fgdbtest
- INTO fgdball;
+ FROM SERVER fgdbtest
+ INTO fgdball;
```
#### Import a Subset of Tables
@@ -315,8 +315,8 @@ For example, the following will only import tables that start with *CitiesIn*. A
CREATE SCHEMA fgdbcityinf;
IMPORT FOREIGN SCHEMA "CitiesIn"
- FROM SERVER fgdbtest
- INTO fgdbcityinf;
+ FROM SERVER fgdbtest
+ INTO fgdbcityinf;
```
You can also use PostgreSQL clauses `LIMIT TO` and `EXCEPT` to restrict the tables you are importing.
```sql
@@ -324,25 +324,25 @@ CREATE SCHEMA fgdbcitysub;
-- import only layer called Cities
IMPORT FOREIGN SCHEMA ogr_all
- LIMIT TO(cities)
- FROM server fgdbtest
- INTO fgdbcitysub ;
+ LIMIT TO(cities)
+ FROM server fgdbtest
+ INTO fgdbcitysub ;
-- import only layers not called Cities or Countries
IMPORT FOREIGN SCHEMA ogr_all
- EXCEPT (cities, countries)
- FROM server fgdbtest
- INTO fgdbcitysub;
+ EXCEPT (cities, countries)
+ FROM server fgdbtest
+ INTO fgdbcitysub;
-- With table laundering turned off, need to use exact layer names
DROP SCHEMA IF EXISTS fgdbcitysub CASCADE;
- -- import with un-laundered table name
+ -- import with un-laundered table name
IMPORT FOREIGN SCHEMA ogr_all
- LIMIT TO("Cities")
- FROM server fgdbtest
- INTO fgdbcitysub
- OPTIONS (launder_table_names 'false') ;
+ LIMIT TO("Cities")
+ FROM server fgdbtest
+ INTO fgdbcitysub
+ OPTIONS (launder_table_names 'false') ;
```
#### Mixed Case and Special Characters
@@ -358,15 +358,15 @@ To preserve casing and other funky characters in both column names and table nam
CREATE SCHEMA fgdbcitypreserve;
IMPORT FOREIGN SCHEMA ogr_all
- FROM SERVER fgdbtest
- INTO fgdbpreserve
- OPTIONS (
- launder_table_names 'false',
- launder_column_names 'false'
- );
+ FROM SERVER fgdbtest
+ INTO fgdbpreserve
+ OPTIONS (
+ launder_table_names 'false',
+ launder_column_names 'false'
+ );
```
-### GDAL Options
+### GDAL Options
The behavior of your GDAL/OGR connection can be altered by passing GDAL `config_options` to the connection when you set up the server. Most GDAL/OGR drivers have some specific behaviours that are controlled by configuration options. For example, the "[ESRI Shapefile](http://www.gdal.org/drv_shapefile.html)" driver includes a `SHAPE_ENCODING` option that controls the character encoding applied to text data.
@@ -374,12 +374,12 @@ Since many Shapefiles are encoded using LATIN1, and most PostgreSQL databases ar
```sql
CREATE SERVER myserver_latin1
- FOREIGN DATA WRAPPER ogr_fdw
- OPTIONS (
- datasource '/tmp/test',
- format 'ESRI Shapefile',
- config_options 'SHAPE_ENCODING=LATIN1'
- );
+ FOREIGN DATA WRAPPER ogr_fdw
+ OPTIONS (
+ datasource '/tmp/test',
+ format 'ESRI Shapefile',
+ config_options 'SHAPE_ENCODING=LATIN1'
+ );
```
Multiple config options can be passed at one time by supplying a **space-separated** list of options.
@@ -388,12 +388,12 @@ If you are using GDAL 2.0 or higher, you can also pass "open options" to your OG
```sql
CREATE SERVER myserver_latin1
- FOREIGN DATA WRAPPER ogr_fdw
- OPTIONS (
- datasource '/tmp/test',
- format 'ESRI Shapefile',
- open_options 'ENCODING=LATIN1'
- );
+ FOREIGN DATA WRAPPER ogr_fdw
+ OPTIONS (
+ datasource '/tmp/test',
+ format 'ESRI Shapefile',
+ open_options 'ENCODING=LATIN1'
+ );
```
### GDAL Debugging
@@ -402,12 +402,12 @@ If you are getting odd behavior and you want to see what GDAL is doing behind th
```sql
CREATE SERVER myserver_latin1
- FOREIGN DATA WRAPPER ogr_fdw
- OPTIONS (
- datasource '/tmp/test',
- format 'ESRI Shapefile',
- config_options 'SHAPE_ENCODING=LATIN1 CPL_DEBUG=ON'
- );
+ FOREIGN DATA WRAPPER ogr_fdw
+ OPTIONS (
+ datasource '/tmp/test',
+ format 'ESRI Shapefile',
+ config_options 'SHAPE_ENCODING=LATIN1 CPL_DEBUG=ON'
+ );
```
GDAL-level messages will be logged at the PostgreSQL **DEBUG2** level, so to see them when running a query, alter your `client_min_messages` setting.
@@ -421,9 +421,9 @@ Once you've figured out your issue, don't forget to remove the `CPL_DEBUG` optio
```sql
SET client_min_messages = notice;
ALTER SERVER myserver_latin1
- OPTIONS (
- SET config_options 'SHAPE_ENCODING=LATIN1'
- );
+ OPTIONS (
+ SET config_options 'SHAPE_ENCODING=LATIN1'
+ );
```
### Utility Functions
@@ -447,17 +447,17 @@ To access sources that have a non-UTF-8 encoding, you may need to specify the ch
```sql
CREATE SERVER odbc_latin1
- FOREIGN DATA WRAPPER ogr_fdw
- OPTIONS (
- datasource 'ODBC:username at servicename',
- format 'ODBC',
- character_encoding 'WIN1250'
- );
+ FOREIGN DATA WRAPPER ogr_fdw
+ OPTIONS (
+ datasource 'ODBC:username at servicename',
+ format 'ODBC',
+ character_encoding 'WIN1250'
+ );
CREATE FOREIGN TABLE featuretable_fdw (
- name text,
- geom geometry(Point, 4326)
+ name text,
+ geom geometry(Point, 4326)
)
SERVER odbc_latin1
- OPTIONS (layer 'featuretable');
+ OPTIONS (layer 'featuretable');
```
=====================================
data/no_geom_apost.csv
=====================================
@@ -3,3 +3,4 @@ Peter,34,10.2
John,77,3.4
Paul,45,19.2
Matthew,35,18.2
+"",44,34.3
=====================================
data/pt_two.prj
=====================================
@@ -1 +1 @@
-GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]]
\ No newline at end of file
+GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295], AUTH["EPSG",4326]]
=====================================
debian/.gitlab-ci.yml
=====================================
@@ -0,0 +1,9 @@
+---
+include:
+ - https://salsa.debian.org/salsa-ci-team/pipeline/raw/master/recipes/debian.yml
+
+variables:
+ RELEASE: 'unstable'
+ SALSA_CI_DISABLE_BUILD_PACKAGE_I386: 1
+ SALSA_CI_DISABLE_REPROTEST: 1
+ SALSA_CI_ENABLE_BUILD_PACKAGE_TWICE: 1
=====================================
debian/changelog
=====================================
@@ -1,3 +1,137 @@
+pgsql-ogr-fdw (1.1.7-1~exp1) experimental; urgency=medium
+
+ * Team upload.
+ * New upstream release.
+ * Bump Standards-Version to 4.7.2, no changes.
+ * Refresh patches.
+
+ -- Bas Couwenberg <sebastic at debian.org> Wed, 21 May 2025 05:21:27 +0200
+
+pgsql-ogr-fdw (1.1.6-1) unstable; urgency=medium
+
+ * Team upload.
+ * New upstream release.
+
+ -- Bas Couwenberg <sebastic at debian.org> Tue, 11 Mar 2025 05:29:09 +0100
+
+pgsql-ogr-fdw (1.1.5-2) unstable; urgency=medium
+
+ * Team upload.
+ * Drop support for 32-bit architectures.
+
+ -- Bas Couwenberg <sebastic at debian.org> Wed, 11 Sep 2024 20:01:27 +0200
+
+pgsql-ogr-fdw (1.1.5-2~exp1) experimental; urgency=medium
+
+ * Team upload.
+ * Bump Standards-Version to 4.7.0, no changes.
+ * Update packaging for postgresql-17.
+
+ -- Bas Couwenberg <sebastic at debian.org> Wed, 11 Sep 2024 11:02:36 +0200
+
+pgsql-ogr-fdw (1.1.5-1) unstable; urgency=medium
+
+ * Team upload.
+ * New upstream release.
+ * Add ${postgresql:Depends} to Depends.
+ * Refresh patches.
+
+ -- Bas Couwenberg <sebastic at debian.org> Fri, 28 Jun 2024 06:13:51 +0200
+
+pgsql-ogr-fdw (1.1.4-3) unstable; urgency=medium
+
+ * Team upload.
+ * Move from experimental to unstable.
+
+ -- Bas Couwenberg <sebastic at debian.org> Sun, 17 Sep 2023 11:17:39 +0200
+
+pgsql-ogr-fdw (1.1.4-3~exp1) experimental; urgency=medium
+
+ * Team upload.
+ * Update packaging for postgresql-16.
+
+ -- Bas Couwenberg <sebastic at debian.org> Sat, 16 Sep 2023 18:36:45 +0200
+
+pgsql-ogr-fdw (1.1.4-2) unstable; urgency=medium
+
+ * Remove newpid from test dependencies.
+
+ -- Christoph Berg <myon at debian.org> Mon, 04 Sep 2023 10:37:00 +0000
+
+pgsql-ogr-fdw (1.1.4-1) unstable; urgency=medium
+
+ * Team upload.
+ * New upstream release.
+ * Add Rules-Requires-Root to control file.
+ * Bump Standards-Version to 4.6.2, no changes.
+ * Bump debhelper compat to 13.
+
+ -- Bas Couwenberg <sebastic at debian.org> Wed, 19 Jul 2023 05:29:25 +0200
+
+pgsql-ogr-fdw (1.1.3-1) unstable; urgency=medium
+
+ * Team upload.
+ * New upstream release.
+ * Enable Salsa CI.
+ * Refresh patches.
+
+ -- Bas Couwenberg <sebastic at debian.org> Tue, 18 Oct 2022 05:21:58 +0200
+
+pgsql-ogr-fdw (1.1.2-2) unstable; urgency=medium
+
+ * Team upload.
+ * Move from experimental to unstable.
+
+ -- Bas Couwenberg <sebastic at debian.org> Sat, 15 Oct 2022 22:02:42 +0200
+
+pgsql-ogr-fdw (1.1.2-2~exp1) experimental; urgency=medium
+
+ * Team upload.
+ * Update packaging for postgresql-15.
+
+ -- Bas Couwenberg <sebastic at debian.org> Sat, 15 Oct 2022 07:41:20 +0200
+
+pgsql-ogr-fdw (1.1.2-1) unstable; urgency=medium
+
+ [ Bas Couwenberg ]
+ * Team upload.
+ * Bump Standards-Version to 4.6.1, no changes.
+
+ [ Christoph Berg ]
+ * New upstream version 1.1.2.
+
+ -- Christoph Berg <myon at debian.org> Fri, 29 Jul 2022 22:12:35 +0200
+
+pgsql-ogr-fdw (1.1.1-3) unstable; urgency=medium
+
+ * Team upload.
+ * Add patch to fix pgsql test failure.
+
+ -- Bas Couwenberg <sebastic at debian.org> Thu, 21 Oct 2021 20:45:09 +0200
+
+pgsql-ogr-fdw (1.1.1-2) unstable; urgency=medium
+
+ * Team upload.
+ * Move from experimental to unstable.
+
+ -- Bas Couwenberg <sebastic at debian.org> Fri, 08 Oct 2021 14:23:12 +0200
+
+pgsql-ogr-fdw (1.1.1-2~exp1) experimental; urgency=medium
+
+ * Team upload.
+ * Bump Standards-Version to 4.6.0, no changes.
+ * Bump debhelper compat to 12, no changes.
+ * Update packaging for postgresql-14.
+
+ -- Bas Couwenberg <sebastic at debian.org> Tue, 05 Oct 2021 21:48:34 +0200
+
+pgsql-ogr-fdw (1.1.1-1) unstable; urgency=medium
+
+ * Team upload.
+ * Move from experimental to unstable.
+
+ -- Bas Couwenberg <sebastic at debian.org> Sun, 15 Aug 2021 16:38:12 +0200
+
pgsql-ogr-fdw (1.1.1-1~exp1) experimental; urgency=medium
* Team upload.
=====================================
debian/compat deleted
=====================================
@@ -1 +0,0 @@
-10
=====================================
debian/control
=====================================
@@ -4,17 +4,20 @@ Uploaders: Michael Fladischer <fladi at debian.org>,
Christoph Berg <myon at debian.org>
Section: database
Priority: optional
-Build-Depends: debhelper (>= 10~),
+Build-Depends: architecture-is-64-bit <!pkg.postgresql.32-bit>,
+ debhelper-compat (= 13),
libgdal-dev,
postgresql-server-dev-all
-Standards-Version: 4.5.1
+Standards-Version: 4.7.2
Vcs-Browser: https://salsa.debian.org/debian-gis-team/pgsql-ogr-fdw
Vcs-Git: https://salsa.debian.org/debian-gis-team/pgsql-ogr-fdw.git -b experimental
Homepage: https://github.com/pramsey/pgsql-ogr-fdw
+Rules-Requires-Root: no
-Package: postgresql-13-ogr-fdw
+Package: postgresql-17-ogr-fdw
Architecture: any
-Depends: postgresql-13,
+Depends: postgresql-17,
+ ${postgresql:Depends},
${shlibs:Depends},
${misc:Depends}
Provides: postgresql-ogr-fdw
=====================================
debian/control.in
=====================================
@@ -4,17 +4,20 @@ Uploaders: Michael Fladischer <fladi at debian.org>,
Christoph Berg <myon at debian.org>
Section: database
Priority: optional
-Build-Depends: debhelper (>= 10~),
+Build-Depends: architecture-is-64-bit <!pkg.postgresql.32-bit>,
+ debhelper-compat (= 13),
libgdal-dev,
postgresql-server-dev-all
-Standards-Version: 4.5.1
+Standards-Version: 4.7.2
Vcs-Browser: https://salsa.debian.org/debian-gis-team/pgsql-ogr-fdw
Vcs-Git: https://salsa.debian.org/debian-gis-team/pgsql-ogr-fdw.git -b experimental
Homepage: https://github.com/pramsey/pgsql-ogr-fdw
+Rules-Requires-Root: no
Package: postgresql-PGVERSION-ogr-fdw
Architecture: any
Depends: postgresql-PGVERSION,
+ ${postgresql:Depends},
${shlibs:Depends},
${misc:Depends}
Provides: postgresql-ogr-fdw
=====================================
debian/patches/hardingflags.patch
=====================================
@@ -3,7 +3,7 @@ Author: Bas Couwenberg <sebastic at debian.org>
--- a/Makefile
+++ b/Makefile
-@@ -49,7 +49,7 @@ CFLAGS = $(GDAL_CFLAGS)
+@@ -73,7 +73,7 @@ CFLAGS = $(GDAL_CFLAGS)
LIBS = $(GDAL_LIBS)
ogr_fdw_info$(X): ogr_fdw_info.o ogr_fdw_common.o stringbuffer.o
=====================================
debian/patches/install.patch
=====================================
@@ -7,7 +7,7 @@ Forwarded: no
--- a/Makefile
+++ b/Makefile
-@@ -55,7 +55,7 @@ clean-exe:
+@@ -79,7 +79,7 @@ clean-exe:
rm -f ogr_fdw_info$(X) ogr_fdw_info.o stringbuffer.o
install-exe: all
=====================================
debian/pgversions
=====================================
@@ -1 +1 @@
-9.6+
+11+
=====================================
debian/rules
=====================================
@@ -12,12 +12,12 @@ include /usr/share/postgresql-common/pgxs_debian_control.mk
%:
dh $@
+override_dh_auto_clean:
+ +pg_buildext clean build-%v
+ $(MAKE) clean
+
override_dh_auto_build:
+pg_buildext build build-%v
override_dh_auto_install:
+pg_buildext install build-%v postgresql-%v-ogr-fdw
-
-override_dh_auto_clean:
- +pg_buildext clean build-%v
- $(MAKE) clean
=====================================
debian/tests/control
=====================================
@@ -1,3 +1,3 @@
-Depends: @, newpid, postgresql-server-dev-all
+Depends: @, postgresql-server-dev-all
Tests: installcheck
-Restrictions: allow-stderr
+Restrictions: allow-stderr, needs-root
=====================================
debian/tests/installcheck
=====================================
@@ -1,9 +1,11 @@
#!/bin/sh
# Force port 5432 and trust because we connect back to ourselves;
-# use newnet to mask any other running instance on 5432.
+# stop any other running instance on 5432.
-newnet pg_buildext -c '--port=5432' -i '--auth=trust' installcheck ||
+service postgresql stop
+
+pg_buildext -c '--port=5432' -i '--auth=trust' installcheck ||
case $(dpkg-architecture -qDEB_BUILD_ARCH_BITS) in
32) echo "Ignoring test failure on 32-bit"; exit 0 ;;
*) exit 1 ;;
=====================================
debian/watch
=====================================
@@ -3,5 +3,5 @@ opts=\
dversionmangle=s/\+(debian|dfsg|ds|deb)\d*$//,\
uversionmangle=s/(\d)[_\.\-\+]?((RC|rc|pre|dev|beta|alpha)\d*)$/$1~$2/;s/RC/rc/;s/\-/\./g,\
filenamemangle=s/(?:.*?)?(?:rel|v|pgsql-ogr-fdw)?[\-\_]?(\d\S+)\.(tgz|tbz|txz|(?:tar\.(?:gz|bz2|xz)))/pgsql-ogr-fdw-$1.$2/ \
-https://github.com/pramsey/pgsql-ogr-fdw/releases \
+https://github.com/pramsey/pgsql-ogr-fdw/tags \
(?:.*?/archive/(?:.*?/)?)?(?:rel|v|pgsql-ogr-fdw)?[\-\_]?(\d[\d\-\.]+)\.(?:tgz|tbz|txz|(?:tar\.(?:gz|bz2|xz)))
=====================================
input/import.source
=====================================
@@ -60,6 +60,8 @@ FROM information_schema._pg_foreign_table_columns AS fc
WHERE fc.nspname = 'imp3' and fc.relname = 'no_geom_apost'
ORDER BY c.ordinal_position;
-SELECT * FROM imp3.no_geom_apost;
+SELECT name,age FROM imp3.no_geom_apost WHERE name = 'Paul';
+SELECT name,age FROM imp3.no_geom_apost WHERE name IS NULL;
+SELECT name,age FROM imp3.no_geom_apost WHERE name = '';
------------------------------------------------
=====================================
input/pgsql.source
=====================================
@@ -19,12 +19,10 @@ CREATE TABLE bytea_local (
----------------------------------------------------------------------
-- Populate local table
-INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
- VALUES ('Jim', '14232'::bytea, 23, 1, 4.3, 5.5, '2010-10-10'::date, '13:23:21'::time, '2010-10-10 13:23:21'::timestamp, 'this', 'y' );
-INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
- VALUES ('Marvin', '55555'::bytea, 34, 2, 5.4, 10.13, '2011-11-11'::date, '15:21:45'::time, '2011-11-11 15:21:45'::timestamp, 'that', 'n' );
-INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
- VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
+INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn) VALUES
+ ('Jim', '14232'::bytea, 23, 1, 4.3, 5.5, '2010-10-10'::date, '13:23:21'::time, '2010-10-10 13:23:21'::timestamp, 'this', 'y' ),
+ ('Marvin', '55555'::bytea, 34, 2, 5.4, 10.13, '2011-11-11'::date, '15:21:45'::time, '2011-11-11 15:21:45'::timestamp, 'that', 'n' ),
+ (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
----------------------------------------------------------------------
-- Create remote table
@@ -134,4 +132,48 @@ SELECT a.fid, a.name, b.name
FROM bytea_local a
JOIN bytea_fdw b
USING (fid);
-
+
+----------------------------------------------------------------------
+-- Populate local array table
+
+SET client_min_messages = NOTICE;
+
+CREATE TABLE array_local (
+ fid integer primary key,
+ geom bytea,
+ txt text[],
+ int int2[],
+ flt float4[],
+ b boolean[]
+);
+
+INSERT INTO array_local (fid,txt, int, flt, b) VALUES
+ (1, ARRAY['Jim'], ARRAY[1,2,3], ARRAY[3.4,5.6,7.8], ARRAY[true,false]),
+ (2, ARRAY['Jim',NULL,'Joe'], ARRAY[1,3,NULL,4], ARRAY[4.5,NULL,3.4], ARRAY[false,NULL]),
+ (3, NULL, NULL, NULL, NULL);
+
+----------------------------------------------------------------------
+-- Create remote array table
+
+CREATE FOREIGN TABLE array_fdw (
+ fid bigint,
+ geom bytea,
+ txt text[],
+ int int4[],
+ flt float8[],
+ b boolean[]
+) SERVER pgserver OPTIONS (layer 'array_local');
+
+SELECT fid, txt, int, flt, b FROM array_fdw;
+
+----------------------------------------------------------------------
+-- Update remote array table
+
+UPDATE array_fdw SET
+ txt = ARRAY['newJim', 'newJoe'],
+ int = ARRAY[-2, -1, 0, 1, 2],
+ flt = ARRAY[-0.1, 0.0, 0.1]
+WHERE fid = 3;
+
+SELECT txt, int, flt FROM array_fdw WHERE fid = 3;
+
=====================================
ogr_fdw.c
=====================================
@@ -39,6 +39,7 @@
*/
#include "ogr_fdw.h"
+
PG_MODULE_MAGIC;
/*
@@ -183,7 +184,7 @@ Oid GEOMETRYOID = InvalidOid;
#if GDAL_VERSION_NUM >= GDAL_COMPUTE_VERSION(2,1,0)
-const char* const gdalErrorTypes[] =
+static const char* const gdalErrorTypes[] =
{
"None",
"AppDefined",
@@ -264,27 +265,112 @@ ogr_fdw_exit(int code, Datum arg)
}
/*
- * Function to get the geometry OID if required
+ * Given extension oid, lookup installation namespace oid.
+ * This side steps search_path issues with
+ * TypenameGetTypid encountered in
+ * https://github.com/pramsey/pgsql-ogr-fdw/issues/263
+ */
+static Oid
+get_extension_nsp_oid(Oid extOid)
+{
+ Oid result;
+ SysScanDesc scandesc;
+ HeapTuple tuple;
+ ScanKeyData entry[1];
+
+#if PG_VERSION_NUM < 120000
+ Relation rel = heap_open(ExtensionRelationId, AccessShareLock);
+ ScanKeyInit(&entry[0],
+ ObjectIdAttributeNumber,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(extOid));
+#else
+ Relation rel = table_open(ExtensionRelationId, AccessShareLock);
+ ScanKeyInit(&entry[0],
+ Anum_pg_extension_oid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(extOid));
+#endif /* PG_VERSION_NUM */
+
+ scandesc = systable_beginscan(rel, ExtensionOidIndexId, true,
+ NULL, 1, entry);
+
+ tuple = systable_getnext(scandesc);
+
+ /* We assume that there can be at most one matching tuple */
+ if (HeapTupleIsValid(tuple))
+ result = ((Form_pg_extension) GETSTRUCT(tuple))->extnamespace;
+ else
+ result = InvalidOid;
+
+ systable_endscan(scandesc);
+
+#if PG_VERSION_NUM < 120000
+ heap_close(rel, AccessShareLock);
+#else
+ table_close(rel, AccessShareLock);
+#endif
+
+ return result;
+}
+
+
+/*
+ * Get the geometry OID (if postgis is
+ * installed) and cache it for quick lookup.
*/
Oid
ogrGetGeometryOid(void)
{
+ /* Is value not set yet? */
if (GEOMETRYOID == InvalidOid)
{
- Oid typoid = TypenameGetTypid("geometry");
- if (OidIsValid(typoid) && get_typisdefined(typoid))
+ const char *extName = "postgis";
+ const char *typName = "geometry";
+ bool missing_ok = true;
+ Oid extOid, extNspOid, typOid;
+
+ /* Got postgis extension? */
+ extOid = get_extension_oid(extName, missing_ok);
+ if (!OidIsValid(extOid))
{
- GEOMETRYOID = typoid;
+ elog(DEBUG2, "%s: lookup of extension '%s' failed", __func__, extName);
+ GEOMETRYOID = BYTEAOID;
+ return GEOMETRYOID;
}
- else
+
+ /* Got namespace for extension? */
+ extNspOid = get_extension_nsp_oid(extOid);
+ if (!OidIsValid(extNspOid))
{
+ elog(DEBUG2, "%s: lookup of namespace for '%s' (%u) failed", __func__, extName, extOid);
GEOMETRYOID = BYTEAOID;
+ return GEOMETRYOID;
}
+
+ /* Got geometry type in namespace? */
+ typOid = GetSysCacheOid2(TYPENAMENSP,
+#if PG_VERSION_NUM >= 120000
+ Anum_pg_type_oid,
+#endif
+ PointerGetDatum(typName),
+ ObjectIdGetDatum(extNspOid));
+
+
+
+ elog(DEBUG2, "%s: lookup of type id for '%s' got %u", __func__, typName, typOid);
+
+ /* Geometry type is good? */
+ if (OidIsValid(typOid) && get_typisdefined(typOid))
+ GEOMETRYOID = typOid;
+ else
+ GEOMETRYOID = BYTEAOID;
}
return GEOMETRYOID;
}
+
/*
* Foreign-data wrapper handler function: return a struct with pointers
* to my callback routines.
@@ -510,6 +596,8 @@ ogrEreportError(const char* errstr)
static void
ogrFinishConnection(OgrConnection* ogr)
{
+ elog(DEBUG3, "%s: entered function", __func__);
+
if (ogr->lyr && OGR_L_SyncToDisk(ogr->lyr) != OGRERR_NONE)
{
elog(NOTICE, "failed to flush writes to OGR data source");
@@ -531,6 +619,8 @@ ogrGetConnectionFromServer(Oid foreignserverid, OgrUpdateable updateable)
ListCell* cell;
OGRErr err;
+ elog(DEBUG3, "%s: entered function", __func__);
+
/* Null all values */
memset(&ogr, 0, sizeof(OgrConnection));
ogr.ds_updateable = OGR_UPDATEABLE_UNSET;
@@ -611,12 +701,16 @@ ogrGetConnectionFromTable(Oid foreigntableid, OgrUpdateable updateable)
ListCell* cell;
OgrConnection ogr;
+ elog(DEBUG3, "%s: entered function", __func__);
+
/* Gather all data for the foreign table. */
table = GetForeignTable(foreigntableid);
/* mapping = GetUserMapping(GetUserId(), table->serverid); */
ogr = ogrGetConnectionFromServer(table->serverid, updateable);
+ elog(DEBUG3, "%s: ogr.ds_str = %s", __func__, ogr.ds_str);
+
foreach (cell, table->options)
{
DefElem* def = (DefElem*) lfirst(cell);
@@ -650,6 +744,8 @@ ogrGetConnectionFromTable(Oid foreigntableid, OgrUpdateable updateable)
elog(ERROR, "FDW table '%s' option is missing", OPT_LAYER);
}
+ elog(DEBUG3, "%s: ogr.lyr_str = %s", __func__, ogr.lyr_str);
+
/* Does the layer exist in the data source? */
ogr.lyr = GDALDatasetGetLayerByName(ogr.ds, ogr.lyr_str);
if (!ogr.lyr)
@@ -858,6 +954,8 @@ ogrGetForeignRelSize(PlannerInfo* root,
OgrFdwPlanState* planstate = (OgrFdwPlanState*)state;
List* scan_clauses = baserel->baserestrictinfo;
+ elog(DEBUG3, "%s: entered function", __func__);
+
/* Set to NULL to clear the restriction clauses in OGR */
OGR_L_SetIgnoredFields(planstate->ogr.lyr, NULL);
OGR_L_SetSpatialFilter(planstate->ogr.lyr, NULL);
@@ -916,6 +1014,8 @@ ogrGetForeignPaths(PlannerInfo* root,
{
OgrFdwPlanState* planstate = (OgrFdwPlanState*)(baserel->fdw_private);
+ elog(DEBUG3, "%s: entered function", __func__);
+
/* TODO: replace this with something that looks at the OGRDriver and */
/* makes a determination based on that? Better: add connection caching */
/* so that slow startup doesn't matter so much */
@@ -935,11 +1035,17 @@ ogrGetForeignPaths(PlannerInfo* root,
NULL, /* PathTarget */
#endif
baserel->rows,
+#if PG_VERSION_NUM >= 180000
+ 0, /* disabled_nodes */
+#endif
planstate->startup_cost,
planstate->total_cost,
NIL, /* no pathkeys */
- NULL, /* no outer rel either */
+ NULL, /* no lateral_relids */
NULL /* no extra plan */
+#if PG_VERSION_NUM >= 170000
+ , NIL /* no fdw_restrictinfo list */
+#endif
#if PG_VERSION_NUM >= 90500
, NIL /* no fdw_private list */
#endif
@@ -947,7 +1053,6 @@ ogrGetForeignPaths(PlannerInfo* root,
); /* no fdw_private data */
}
-
/*
* Convert an OgrFdwSpatialFilter into a List so it can
* be safely passed through the fdw_private list.
@@ -1016,6 +1121,8 @@ ogrGetForeignPlan(PlannerInfo* root,
OgrFdwSpatialFilter* spatial_filter = NULL;
char* attribute_filter = NULL;
+ elog(DEBUG3, "%s: entered function", __func__);
+
/* Add in column mapping data to build SQL with the right OGR column names */
ogrReadColumnData(state);
@@ -1076,175 +1183,137 @@ ogrGetForeignPlan(PlannerInfo* root,
}
-static void
-pgCanConvertToOgr(Oid pg_type, OGRFieldType ogr_type, const char* colname, const char* tblname)
+static bool
+pgCanConvertToOgr(Oid pg_type, OGRFieldType ogr_type)
{
- if (pg_type == BOOLOID && ogr_type == OFTInteger)
- {
- return;
- }
- else if (pg_type == INT2OID && ogr_type == OFTInteger)
- {
- return;
- }
- else if (pg_type == INT4OID && ogr_type == OFTInteger)
- {
- return;
- }
- else if (pg_type == INT8OID)
+ struct PgOgrMap
{
+ Oid pg;
+ OGRFieldType ogr;
+ };
+
+ static struct PgOgrMap data[] = {
+ {BOOLOID, OFTInteger}, /* 16 */
+ {BYTEAOID, OFTBinary}, /* 17 */
+ {CHAROID, OFTString}, /* 18 */
+ {NAMEOID, OFTString}, /* 19 */
#if GDAL_VERSION_MAJOR >= 2
- if (ogr_type == OFTInteger64)
- {
- return;
- }
+ {INT8OID, OFTInteger64}, /* 20 */
#else
- if (ogr_type == OFTInteger)
- {
- return;
- }
+ {INT8OID, OFTInteger}, /* 20 */
#endif
+ {INT2OID, OFTInteger}, /* 21 */
+ {INT4OID, OFTInteger}, /* 23 */
+ {TEXTOID, OFTString}, /* 25 */
+ {FLOAT4OID, OFTReal}, /* 700 */
+ {FLOAT8OID, OFTReal}, /* 701 */
+ {BOOLARRAYOID, OFTIntegerList}, /* 1000 */
+ {CHARARRAYOID, OFTStringList}, /* 1002 */
+ {NAMEARRAYOID, OFTStringList}, /* 1003 */
+ {INT2ARRAYOID, OFTIntegerList}, /* 1005 */
+ {INT4ARRAYOID, OFTIntegerList}, /* 1007 */
+ {TEXTARRAYOID, OFTStringList}, /* 1009 */
+ {VARCHARARRAYOID, OFTStringList}, /* 1015 */
+#if GDAL_VERSION_MAJOR >= 2
+ {INT8ARRAYOID, OFTInteger64List}, /* 1016 */
+#endif
+ {FLOAT4ARRAYOID, OFTRealList}, /* 1021 */
+ {FLOAT8ARRAYOID, OFTRealList}, /* 1022 */
+ {BPCHAROID, OFTString}, /* 1042 */
+ {VARCHAROID, OFTString}, /* 1043 */
+ {DATEOID, OFTDate}, /* 1082 */
+ {TIMEOID, OFTTime}, /* 1083 */
+ {TIMESTAMPOID, OFTDateTime}, /* 1114 */
+ {NUMERICOID, OFTReal}, /* 1700 */
+ {0, 0}};
+
+ struct PgOgrMap* map = data;
+ while (map->pg)
+ {
+ if (ogr_type == map->ogr) return true;
+ else map++;
}
- else if (pg_type == NUMERICOID && ogr_type == OFTReal)
- {
- return;
- }
- else if (pg_type == FLOAT4OID && ogr_type == OFTReal)
- {
- return;
- }
- else if (pg_type == FLOAT8OID && ogr_type == OFTReal)
- {
- return;
- }
- else if (pg_type == TEXTOID && ogr_type == OFTString)
- {
- return;
- }
- else if (pg_type == VARCHAROID && ogr_type == OFTString)
- {
- return;
- }
- else if (pg_type == CHAROID && ogr_type == OFTString)
- {
- return;
- }
- else if (pg_type == BPCHAROID && ogr_type == OFTString)
- {
- return;
- }
- else if (pg_type == NAMEOID && ogr_type == OFTString)
- {
- return;
- }
- else if (pg_type == BYTEAOID && ogr_type == OFTBinary)
- {
- return;
- }
- else if (pg_type == DATEOID && ogr_type == OFTDate)
- {
- return;
- }
- else if (pg_type == TIMEOID && ogr_type == OFTTime)
- {
- return;
- }
- else if (pg_type == TIMESTAMPOID && ogr_type == OFTDateTime)
- {
- return;
- }
+ return false;
- ereport(ERROR, (
- errcode(ERRCODE_FDW_INVALID_DATA_TYPE),
- errmsg("column \"%s\" of foreign table \"%s\" converts \"%s\" to OGR \"%s\"",
- colname, tblname,
- format_type_be(pg_type), OGR_GetFieldTypeName(ogr_type))
- ));
}
+
static void
-ogrCanConvertToPg(OGRFieldType ogr_type, Oid pg_type, const char* colname, const char* tblname)
+pgCheckConvertToOgr(Oid pg_type, OGRFieldType ogr_type, const char *colname, const char *tblname)
{
- switch (ogr_type)
- {
- case OFTInteger:
- if (pg_type == BOOLOID || pg_type == INT4OID || pg_type == INT8OID ||
- pg_type == NUMERICOID || pg_type == FLOAT4OID ||
- pg_type == FLOAT8OID || pg_type == TEXTOID || pg_type == VARCHAROID)
- {
- return;
- }
- break;
-
- case OFTReal:
- if (pg_type == NUMERICOID || pg_type == FLOAT4OID || pg_type == FLOAT8OID ||
- pg_type == TEXTOID || pg_type == VARCHAROID)
- {
- return;
- }
- break;
+ if (pgCanConvertToOgr(pg_type, ogr_type))
+ return;
- case OFTBinary:
- if (pg_type == BYTEAOID)
- {
- return;
- }
- break;
+ ereport(ERROR, (
+ errcode(ERRCODE_FDW_INVALID_DATA_TYPE),
+ errmsg("column \"%s\" of foreign table \"%s\" converts \"%s\" to OGR \"%s\"",
+ colname, tblname,
+ format_type_be(pg_type), OGR_GetFieldTypeName(ogr_type))
+ ));
+}
- case OFTString:
- if (pg_type == TEXTOID || pg_type == VARCHAROID || pg_type == CHAROID || pg_type == BPCHAROID)
- {
- return;
- }
- break;
- case OFTDate:
- if (pg_type == DATEOID || pg_type == TIMESTAMPOID || pg_type == TEXTOID || pg_type == VARCHAROID)
- {
- return;
- }
- break;
-
- case OFTTime:
- if (pg_type == TIMEOID || pg_type == TEXTOID || pg_type == VARCHAROID)
+static bool
+ogrCanConvertToPg(OGRFieldType ogr_type, Oid pg_type)
+{
+ struct OgrPgMap
+ {
+ OGRFieldType ogr;
+ Oid pg[16];
+ };
+
+ static struct OgrPgMap data[] =
+ {
+ {OFTInteger, {BOOLOID, INT4OID, INT8OID, NUMERICOID, FLOAT4OID, FLOAT8OID, TEXTOID, VARCHAROID, 0}},
+ {OFTReal, {NUMERICOID, FLOAT4OID, FLOAT8OID, TEXTOID, VARCHAROID, 0}},
+ {OFTBinary, {BYTEAOID, 0}},
+ {OFTString, {TEXTOID, VARCHAROID, CHAROID, BPCHAROID, JSONBOID, JSONOID, 0}},
+ {OFTDate, {DATEOID, TIMESTAMPOID, TEXTOID, VARCHAROID, 0}},
+ {OFTTime, {TIMEOID, TEXTOID, VARCHAROID, 0}},
+ {OFTDateTime, {TIMESTAMPOID, TEXTOID, VARCHAROID, 0}},
+ #if GDAL_VERSION_MAJOR >= 2
+ {OFTInteger64, {INT8OID, NUMERICOID, FLOAT8OID, TEXTOID, VARCHAROID, 0}},
+ {OFTInteger64List, {INT8ARRAYOID, FLOAT8ARRAYOID, TEXTARRAYOID, VARCHARARRAYOID, 0}},
+ #endif
+ {OFTRealList, {FLOAT4ARRAYOID, FLOAT8ARRAYOID, TEXTARRAYOID, VARCHARARRAYOID, 0}},
+ {OFTStringList, {TEXTARRAYOID, VARCHARARRAYOID, NAMEARRAYOID, CHARARRAYOID, 0}},
+ {OFTIntegerList, {BOOLARRAYOID, INT2ARRAYOID, INT4ARRAYOID, INT8ARRAYOID, TEXTARRAYOID, VARCHARARRAYOID, 0}},
+ {256, {0}} /* Zero terminate list */
+ };
+
+ struct OgrPgMap* map = data;
+ while (map->ogr <= OFTMaxType)
+ {
+ if (ogr_type == map->ogr)
{
- return;
+ Oid *typ = map->pg;
+ while (*typ)
+ {
+ if (pg_type == *typ) return true;
+ else typ++;
+ }
}
- break;
+ map++;
+ }
- case OFTDateTime:
- if (pg_type == TIMESTAMPOID || pg_type == TEXTOID || pg_type == VARCHAROID)
- {
- return;
- }
- break;
+ return false;
+}
-#if GDAL_VERSION_MAJOR >= 2
- case OFTInteger64:
- if (pg_type == INT8OID || pg_type == NUMERICOID || pg_type == FLOAT8OID ||
- pg_type == TEXTOID || pg_type == VARCHAROID)
- {
- return;
- }
- break;
-#endif
+static void
+ogrCheckConvertToPg(OGRFieldType ogr_type, Oid pg_type, const char *colname, const char *tblname)
+{
+ if (ogrCanConvertToPg(ogr_type, pg_type))
+ return;
- case OFTWideString:
- case OFTIntegerList:
-#if GDAL_VERSION_MAJOR >= 2
- case OFTInteger64List:
-#endif
- case OFTRealList:
- case OFTStringList:
- case OFTWideStringList:
+ if (ogr_type == OFTWideString || ogr_type == OFTWideStringList)
{
ereport(ERROR, (
errcode(ERRCODE_FDW_INVALID_DATA_TYPE),
- errmsg("column \"%s\" of foreign table \"%s\" uses an OGR array, currently unsupported", colname, tblname)
+ errmsg("column \"%s\" of foreign table \"%s\" uses an OGR OFTWideString, deprecated", colname, tblname)
));
- break;
- }
+ return;
}
+
ereport(ERROR, (
errcode(ERRCODE_FDW_INVALID_DATA_TYPE),
errmsg("column \"%s\" of foreign table \"%s\" converts OGR \"%s\" to \"%s\"",
@@ -1253,6 +1322,7 @@ ogrCanConvertToPg(OGRFieldType ogr_type, Oid pg_type, const char* colname, const
));
}
+
#ifdef OGR_FDW_HEXWKB
static char* hexchr = "0123456789ABCDEF";
@@ -1399,11 +1469,8 @@ ogrReadColumnData(OgrFdwState* state)
OgrFieldEntry* found_entry;
OgrFieldEntry entry;
-#if PG_VERSION_NUM >= 110000
- Form_pg_attribute att_tuple = &tupdesc->attrs[i];
-#else
- Form_pg_attribute att_tuple = tupdesc->attrs[i];
-#endif
+ Form_pg_attribute att_tuple = TupleDescAttr(tupdesc, i);
+
OgrFdwColumn col = tbl->cols[i];
col.pgattnum = att_tuple->attnum;
col.pgtype = att_tuple->atttypid;
@@ -1416,17 +1483,27 @@ ogrReadColumnData(OgrFdwState* state)
continue;
}
+ /* Check for array type */
+ col.pgelmtype = get_element_type(col.pgtype);
+ if (col.pgelmtype)
+ {
+ /* Extra type info needed to form the array */
+ col.pgisarray = true;
+ }
+ else
+ col.pgelmtype = col.pgtype;
+
/* Find the appropriate conversion functions */
- getTypeInputInfo(col.pgtype, &col.pginputfunc, &col.pginputioparam);
- getTypeBinaryInputInfo(col.pgtype, &col.pgrecvfunc, &col.pgrecvioparam);
- getTypeOutputInfo(col.pgtype, &col.pgoutputfunc, &col.pgoutputvarlena);
- getTypeBinaryOutputInfo(col.pgtype, &col.pgsendfunc, &col.pgsendvarlena);
+ getTypeInputInfo(col.pgelmtype, &col.pginputfunc, &col.pginputioparam);
+ getTypeBinaryInputInfo(col.pgelmtype, &col.pgrecvfunc, &col.pgrecvioparam);
+ getTypeOutputInfo(col.pgelmtype, &col.pgoutputfunc, &col.pgoutputvarlena);
+ getTypeBinaryOutputInfo(col.pgelmtype, &col.pgsendfunc, &col.pgsendvarlena);
/* Get the PgSQL column name */
#if PG_VERSION_NUM >= 110000
- col.pgname = get_attname(rel->rd_id, att_tuple->attnum, false);
+ col.pgname = pstrdup(get_attname(rel->rd_id, att_tuple->attnum, false));
#else
- col.pgname = get_attname(rel->rd_id, att_tuple->attnum);
+ col.pgname = pstrdup(get_attname(rel->rd_id, att_tuple->attnum));
#endif
/* Handle FID first */
@@ -1487,7 +1564,7 @@ ogrReadColumnData(OgrFdwState* state)
OGRFieldType fldtype = OGR_Fld_GetType(fld);
/* Error if types mismatched when column names match */
- ogrCanConvertToPg(fldtype, col.pgtype, col.pgname, tblname);
+ ogrCheckConvertToPg(fldtype, col.pgtype, col.pgname, tblname);
col.ogrvariant = OGR_FIELD;
col.ogrfldnum = found_entry->fldnum;
@@ -1525,53 +1602,77 @@ ogrReadColumnData(OgrFdwState* state)
return;
}
+
/*
* ogrLookupGeometryFunctionOid
*
* Find the procedure Oids of useful functions so we can call
- * them later.
+ * them later. In the case where multiple functions have the
+ * same signature, in different namespaces (???) we might have
+ * problems, but that seems very unlikely.
+ *
+ * https://github.com/pramsey/pgsql-ogr-fdw/issues/266
*/
static Oid
ogrLookupGeometryFunctionOid(const char* proname)
{
- List* names;
- FuncCandidateList clist;
+ CatCList *clist;
+ int i;
+ Oid result = InvalidOid;
/* This only works if PostGIS is installed */
- if (ogrGetGeometryOid() == InvalidOid || ogrGetGeometryOid() == BYTEAOID)
+ if (ogrGetGeometryOid() == InvalidOid ||
+ ogrGetGeometryOid() == BYTEAOID)
{
- return InvalidOid;
+ return result;
}
- names = stringToQualifiedNameList(proname);
-#if PG_VERSION_NUM < 90400
- clist = FuncnameGetCandidates(names, -1, NIL, false, false);
-#elif PG_VERSION_NUM < 140000
- clist = FuncnameGetCandidates(names, -1, NIL, false, false, false);
+ if (!proname) return result;
+
+ /* Search syscache by name only */
+ clist = SearchSysCacheList1(PROCNAMEARGSNSP,
+ CStringGetDatum(proname));
+
+ if (!clist) return InvalidOid;
+
+ for (i = 0; i < clist->n_members; i++)
+ {
+ HeapTuple proctup = &clist->members[i]->tuple;
+ Form_pg_proc procform = (Form_pg_proc) GETSTRUCT(proctup);
+ Oid *proargtypes = procform->proargtypes.values;
+ int pronargs = procform->pronargs;
+
+#if PG_VERSION_NUM >= 120000
+ Oid procoid = procform->oid;
#else
- clist = FuncnameGetCandidates(names, -1, NIL, false, false, false, false);
+ Oid procoid = HeapTupleGetOid(proctup);
#endif
- if (streq(proname, "st_setsrid"))
- {
- do
+
+ /* ST_SetSRID(geometry, integer) */
+ if (streq(proname, "st_setsrid") &&
+ pronargs == 2 &&
+ proargtypes[0] == ogrGetGeometryOid())
{
- int i;
- for (i = 0; i < clist->nargs; i++)
- {
- if (clist->args[i] == ogrGetGeometryOid())
- {
- return clist->oid;
- }
- }
+ result = procoid;
+ break;
+ }
+ /* postgis_typmod_srid(typmod) */
+ else if (streq(proname, "postgis_typmod_srid") &&
+ pronargs == 1)
+ {
+ /* postgis_typmod_srid(integer) */
+ result = procoid;
+ break;
+ }
+ else
+ {
+ elog(ERROR, "%s could not find function '%s'",
+ __func__, proname);
}
- while ((clist = clist->next));
- }
- else if (streq(proname, "postgis_typmod_srid"))
- {
- return clist->oid;
}
- return InvalidOid;
+ ReleaseSysCacheList(clist);
+ return result;
}
/*
@@ -1586,6 +1687,8 @@ ogrBeginForeignScan(ForeignScanState* node, int eflags)
Oid foreigntableid = RelationGetRelid(node->ss.ss_currentRelation);
ForeignScan* fsplan = (ForeignScan*)node->ss.ps.plan;
+ elog(DEBUG3, "%s: entered function", __func__);
+
/* Do nothing in EXPLAIN (no ANALYZE) case */
if (eflags & EXEC_FLAG_EXPLAIN_ONLY)
return;
@@ -1661,18 +1764,38 @@ ogrBeginForeignScan(ForeignScanState* node, int eflags)
* each column in the foreign table.
*/
static Datum
-pgDatumFromCString(const char* cstr, Oid pgtype, int pgtypmod, Oid pginputfunc)
+pgDatumFromCString(const char* cstr, const OgrFdwColumn *col, int char_encoding, bool *is_null)
{
- Datum value;
- Datum cdata = CStringGetDatum(cstr);
+ size_t cstr_len = cstr ? strlen(cstr) : 0;
+ Datum value = (Datum) 0;
+ char *cstr_decoded;
+
+ /* Zero length implies NULL for all non-strings */
+ if (cstr_len == 0 && (col->ogrfldtype != OFTString && col->ogrfldtype != OFTStringList))
+ {
+ *is_null = true;
+ return value;
+ }
+
+ cstr_decoded = char_encoding ?
+ pg_any_to_server(cstr, cstr_len, char_encoding) :
+ pstrdup(cstr);
- value = OidFunctionCall3(pginputfunc, cdata,
- ObjectIdGetDatum(InvalidOid),
- Int32GetDatum(pgtypmod));
+ value = OidFunctionCall3(col->pginputfunc,
+ CStringGetDatum(cstr_decoded),
+ ObjectIdGetDatum(InvalidOid),
+ Int32GetDatum(col->pgtypmod));
+ /* Free cstr_decoded if it is a copy */
+ if (cstr != cstr_decoded)
+ pfree(cstr_decoded);
+
+ is_null = false;
return value;
}
+
+
static inline void
ogrNullSlot(Datum* values, bool* nulls, int i)
{
@@ -1704,6 +1827,9 @@ ogrFeatureToSlot(const OGRFeatureH feat, TupleTableSlot* slot, const OgrFdwExecS
TupleDesc tupdesc = slot->tts_tupleDescriptor;
int have_typmod_funcs = (execstate->setsridfunc && execstate->typmodsridfunc);
+#define CSTR_SZ 256
+ char cstr[CSTR_SZ];
+
/* Check our assumption that slot and setup data match */
if (tbl->ncols != tupdesc->natts)
{
@@ -1717,8 +1843,6 @@ ogrFeatureToSlot(const OGRFeatureH feat, TupleTableSlot* slot, const OgrFdwExecS
OgrFdwColumn col = tbl->cols[i];
const char* pgname = col.pgname;
Oid pgtype = col.pgtype;
- int pgtypmod = col.pgtypmod;
- Oid pginputfunc = col.pginputfunc;
int ogrfldnum = col.ogrfldnum;
OGRFieldType ogrfldtype = col.ogrfldtype;
OgrColumnVariant ogrvariant = col.ogrvariant;
@@ -1735,6 +1859,7 @@ ogrFeatureToSlot(const OGRFeatureH feat, TupleTableSlot* slot, const OgrFdwExecS
if (ogrvariant == OGR_FID)
{
GIntBig fid = OGR_F_GetFID(feat);
+ bool is_null = false;
if (fid == OGRNullFID)
{
@@ -1745,8 +1870,8 @@ ogrFeatureToSlot(const OGRFeatureH feat, TupleTableSlot* slot, const OgrFdwExecS
char fidstr[256];
snprintf(fidstr, 256, OGR_FDW_FRMT_INT64, OGR_FDW_CAST_INT64(fid));
- nulls[i] = false;
- values[i] = pgDatumFromCString(fidstr, pgtype, pgtypmod, pginputfunc);
+ values[i] = pgDatumFromCString(fidstr, &col, execstate->ogr.char_encoding, &is_null);
+ nulls[i] = is_null;
}
}
else if (ogrvariant == OGR_GEOMETRY)
@@ -1868,118 +1993,175 @@ ogrFeatureToSlot(const OGRFeatureH feat, TupleTableSlot* slot, const OgrFdwExecS
#endif
/* Ensure that the OGR data type fits the destination Pg column */
- ogrCanConvertToPg(ogrfldtype, pgtype, pgname, tbl->tblname);
+ ogrCheckConvertToPg(ogrfldtype, pgtype, pgname, tbl->tblname);
/* Only convert non-null fields */
- if (field_not_null)
+ if (!field_not_null)
{
- switch (ogrfldtype)
- {
- case OFTBinary:
- {
- /*
- * Convert binary fields to bytea directly
- */
- int bufsize;
- GByte* buf = OGR_F_GetFieldAsBinary(feat, ogrfldnum, &bufsize);
- int varsize = bufsize + VARHDRSZ;
- bytea* varlena = palloc(varsize);
- memcpy(VARDATA(varlena), buf, bufsize);
- SET_VARSIZE(varlena, varsize);
- nulls[i] = false;
- values[i] = PointerGetDatum(varlena);
- break;
- }
- case OFTInteger:
- case OFTReal:
- case OFTString:
+ ogrNullSlot(values, nulls, i);
+ continue;
+ }
+
+ switch (ogrfldtype)
+ {
+ case OFTBinary:
+ {
+ /*
+ * Convert binary fields to bytea directly
+ */
+ int bufsize;
+ GByte* buf = OGR_F_GetFieldAsBinary(feat, ogrfldnum, &bufsize);
+ int varsize = bufsize + VARHDRSZ;
+ bytea* varlena = palloc(varsize);
+ memcpy(VARDATA(varlena), buf, bufsize);
+ SET_VARSIZE(varlena, varsize);
+ nulls[i] = false;
+ values[i] = PointerGetDatum(varlena);
+ break;
+ }
+ case OFTInteger:
+ case OFTReal:
+ case OFTString:
#if GDAL_VERSION_MAJOR >= 2
- case OFTInteger64:
+ case OFTInteger64:
#endif
+ {
+ /*
+ * Convert numbers and strings via a string representation.
+ * Handling numbers directly would be faster, but require a lot of extra code.
+ * For now, we go via text.
+ */
+ const char* cstr_in = OGR_F_GetFieldAsString(feat, ogrfldnum);
+ bool is_null = false;
+ values[i] = pgDatumFromCString(cstr_in, &col, execstate->ogr.char_encoding, &is_null);
+ nulls[i] = is_null;
+ break;
+ }
+ case OFTDate:
+ case OFTTime:
+ case OFTDateTime:
+ {
+ /*
+ * OGR date/times have a weird access method, so we use that to pull
+ * out the raw data and turn it into a string for PgSQL's (very
+ * sophisticated) date/time parsing routines to handle.
+ */
+ int year, month, day, hour, minute, second, tz;
+ bool is_null = false;
+ OGR_F_GetFieldAsDateTime(feat, ogrfldnum,
+ &year, &month, &day,
+ &hour, &minute, &second, &tz);
+
+ if (ogrfldtype == OFTDate)
{
- /*
- * Convert numbers and strings via a string representation.
- * Handling numbers directly would be faster, but require a lot of extra code.
- * For now, we go via text.
- */
- const char* cstr_in = OGR_F_GetFieldAsString(feat, ogrfldnum);
- size_t cstr_len = cstr_in ? strlen(cstr_in) : 0;
- if (cstr_in && cstr_len > 0)
- {
- char* cstr_decoded;
- if (execstate->ogr.char_encoding)
- {
- cstr_decoded = pg_any_to_server(cstr_in, cstr_len, execstate->ogr.char_encoding);
- }
- else
- {
- cstr_decoded = pstrdup(cstr_in);
- }
- nulls[i] = false;
- values[i] = pgDatumFromCString(cstr_decoded, pgtype, pgtypmod, pginputfunc);
- /* Free cstr_decoded if it is a copy */
- if (cstr_in != cstr_decoded)
- pfree(cstr_decoded);
- }
- else
- {
- ogrNullSlot(values, nulls, i);
- }
- break;
+ snprintf(cstr, CSTR_SZ, "%d-%02d-%02d", year, month, day);
}
- case OFTDate:
- case OFTTime:
- case OFTDateTime:
+ else if (ogrfldtype == OFTTime)
{
- /*
- * OGR date/times have a weird access method, so we use that to pull
- * out the raw data and turn it into a string for PgSQL's (very
- * sophisticated) date/time parsing routines to handle.
- */
- int year, month, day, hour, minute, second, tz;
- char cstr[256];
-
- OGR_F_GetFieldAsDateTime(feat, ogrfldnum,
- &year, &month, &day,
- &hour, &minute, &second, &tz);
-
- if (ogrfldtype == OFTDate)
- {
- snprintf(cstr, 256, "%d-%02d-%02d", year, month, day);
- }
- else if (ogrfldtype == OFTTime)
- {
- snprintf(cstr, 256, "%02d:%02d:%02d", hour, minute, second);
- }
- else
- {
- snprintf(cstr, 256, "%d-%02d-%02d %02d:%02d:%02d", year, month, day, hour, minute, second);
- }
- nulls[i] = false;
- values[i] = pgDatumFromCString(cstr, pgtype, pgtypmod, pginputfunc);
- break;
+ snprintf(cstr, CSTR_SZ, "%02d:%02d:%02d", hour, minute, second);
+ }
+ else
+ {
+#if (GDAL_VERSION_NUM >= GDAL_COMPUTE_VERSION(3,7,0))
+ const char* tsstr = OGR_F_GetFieldAsISO8601DateTime(feat, ogrfldnum, NULL);
+ strncpy(cstr, tsstr, CSTR_SZ);
+#else
+ snprintf(cstr, CSTR_SZ, "%d-%02d-%02d %02d:%02d:%02d", year, month, day, hour, minute, second);
+#endif
+ }
+ values[i] = pgDatumFromCString(cstr, &col, PG_SQL_ASCII, &is_null);
+ nulls[i] = is_null;
+ break;
+
+ }
+#if GDAL_VERSION_MAJOR >= 2
+ case OFTInteger64List:
+ {
+ int ilist_size;
+ const int64 *ilist = (int64*)OGR_F_GetFieldAsInteger64List(feat, ogrfldnum, &ilist_size);
+ ArrayBuildState *abs = initArrayResult(col.pgelmtype, CurrentMemoryContext, false);
+ for (uint32 i = 0; i < ilist_size; i++)
+ {
+ bool is_null = false;
+ snprintf(cstr, CSTR_SZ, OGR_FDW_FRMT_INT64, OGR_FDW_CAST_INT64(ilist[i]));
+ abs = accumArrayResult(abs,
+ pgDatumFromCString(cstr, &col, execstate->ogr.char_encoding, &is_null),
+ is_null,
+ col.pgelmtype,
+ CurrentMemoryContext);
}
- case OFTIntegerList:
- case OFTRealList:
- case OFTStringList:
+ values[i] = makeArrayResult(abs, CurrentMemoryContext);
+ nulls[i] = false;
+ break;
+ }
+#endif
+
+ case OFTIntegerList:
+ {
+ int ilist_size;
+ const int *ilist = OGR_F_GetFieldAsIntegerList(feat, ogrfldnum, &ilist_size);
+ ArrayBuildState *abs = initArrayResult(col.pgelmtype, CurrentMemoryContext, false);
+ for (uint32 i = 0; i < ilist_size; i++)
{
- /* TODO, map these OGR array types into PgSQL arrays (fun!) */
- elog(ERROR, "unsupported OGR array type \"%s\"", OGR_GetFieldTypeName(ogrfldtype));
- break;
+ bool is_null = false;
+ snprintf(cstr, CSTR_SZ, "%d", ilist[i]);
+ abs = accumArrayResult(abs,
+ pgDatumFromCString(cstr, &col, execstate->ogr.char_encoding, &is_null),
+ is_null,
+ col.pgelmtype,
+ CurrentMemoryContext);
}
- default:
+ values[i] = makeArrayResult(abs, CurrentMemoryContext);
+ nulls[i] = false;
+ break;
+ }
+
+ case OFTRealList:
+ {
+ int rlist_size;
+ const double *rlist = OGR_F_GetFieldAsDoubleList(feat, ogrfldnum, &rlist_size);
+ ArrayBuildState *abs = initArrayResult(col.pgelmtype, CurrentMemoryContext, false);
+ for (uint32 i = 0; i < rlist_size; i++)
{
- elog(ERROR, "unsupported OGR type \"%s\"", OGR_GetFieldTypeName(ogrfldtype));
- break;
+ bool is_null = false;
+ snprintf(cstr, CSTR_SZ, "%g", rlist[i]);
+ abs = accumArrayResult(abs,
+ pgDatumFromCString(cstr, &col, execstate->ogr.char_encoding, &is_null),
+ is_null,
+ col.pgelmtype,
+ CurrentMemoryContext);
}
+ values[i] = makeArrayResult(abs, CurrentMemoryContext);
+ nulls[i] = false;
+ break;
+ }
+ case OFTStringList:
+ {
+ ArrayBuildState *abs = initArrayResult(col.pgelmtype, CurrentMemoryContext, false);
+ char **cstrs = OGR_F_GetFieldAsStringList(feat, ogrfldnum);
+ while (*cstrs)
+ {
+ bool is_null = false;
+ abs = accumArrayResult(abs,
+ pgDatumFromCString(*cstrs, &col, execstate->ogr.char_encoding, &is_null),
+ is_null,
+ col.pgelmtype,
+ CurrentMemoryContext);
+ cstrs++;
}
+ values[i] = makeArrayResult(abs, CurrentMemoryContext);
+ nulls[i] = false;
+ break;
}
- else
+ default:
{
- ogrNullSlot(values, nulls, i);
+ elog(ERROR, "unsupported OGR type \"%s\"", OGR_GetFieldTypeName(ogrfldtype));
+ break;
}
+
+ } /* !switch */
}
/* Fill in unmatched columns with NULL */
else if (ogrvariant == OGR_UNMATCHED)
@@ -2160,7 +2342,7 @@ ogrSlotToFeature(const TupleTableSlot* slot, OGRFeatureH feat, const OgrFdwTable
else if (ogrvariant == OGR_FIELD)
{
/* Ensure that the OGR data type fits the destination Pg column */
- pgCanConvertToOgr(pgtype, ogrfldtype, pgname, tbl->tblname);
+ pgCheckConvertToOgr(pgtype, ogrfldtype, pgname, tbl->tblname);
/* Skip NULL case */
if (nulls[i])
@@ -2303,6 +2485,70 @@ ogrSlotToFeature(const TupleTableSlot* slot, OGRFeatureH feat, const OgrFdwTable
break;
}
+ case BOOLARRAYOID:
+ case INT2ARRAYOID:
+ case INT4ARRAYOID:
+ {
+ ArrayType *arr = DatumGetArrayTypeP(values[i]);
+ size_t sz = ArrayGetNItems(ARR_NDIM(arr), ARR_DIMS(arr));
+ Datum d;
+ bool isnull;
+ int *ints = palloc(sizeof(int) * sz);
+ int num_ints = 0;
+ ArrayIterator it = array_create_iterator(arr, 0, NULL);
+ while (array_iterate(it, &d, &isnull))
+ {
+ if (isnull) continue;
+ ints[num_ints++] = DatumGetInt32(d);
+ }
+ OGR_F_SetFieldIntegerList(feat, ogrfldnum, num_ints, ints);
+ pfree(ints);
+ break;
+ }
+
+ case CHARARRAYOID:
+ case NAMEARRAYOID:
+ case TEXTARRAYOID:
+ case VARCHARARRAYOID:
+ {
+ ArrayType *arr = DatumGetArrayTypeP(values[i]);
+ Datum d;
+ bool isnull;
+ char** papszList = NULL;
+ ArrayIterator it = array_create_iterator(arr, 0, NULL);
+ while (array_iterate(it, &d, &isnull))
+ {
+ char *cstr;
+ if (isnull) continue;
+ cstr = text_to_cstring(DatumGetTextP(d));
+ papszList = CSLAddString(papszList, cstr);
+ pfree(cstr);
+ }
+ OGR_F_SetFieldStringList(feat, ogrfldnum, papszList);
+ CSLDestroy(papszList);
+ break;
+ }
+
+ case FLOAT4ARRAYOID:
+ case FLOAT8ARRAYOID:
+ {
+ ArrayType *arr = DatumGetArrayTypeP(values[i]);
+ size_t sz = ArrayGetNItems(ARR_NDIM(arr), ARR_DIMS(arr));
+ Datum d;
+ bool isnull;
+ double *floats = palloc(sizeof(double) * sz);
+ int num_floats = 0;
+ ArrayIterator it = array_create_iterator(arr, 0, NULL);
+ while (array_iterate(it, &d, &isnull))
+ {
+ if (isnull) continue;
+ floats[num_floats++] = DatumGetFloat8(d);
+ }
+ OGR_F_SetFieldDoubleList(feat, ogrfldnum, num_floats, floats);
+ pfree(floats);
+ break;
+ }
+
/* TODO: array types for string, integer, float */
default:
{
@@ -2340,6 +2586,8 @@ ogrIterateForeignScan(ForeignScanState* node)
TupleTableSlot* slot = node->ss.ss_ScanTupleSlot;
OGRFeatureH feat;
+ elog(DEBUG3, "%s: entered function", __func__);
+
/*
* Clear the slot. If it gets through w/o being filled up, that means
* we're all done.
@@ -2387,6 +2635,7 @@ static void
ogrReScanForeignScan(ForeignScanState* node)
{
OgrFdwExecState* execstate = (OgrFdwExecState*) node->fdw_state;
+ elog(DEBUG3, "%s: entered function", __func__);
OGR_L_ResetReading(execstate->ogr.lyr);
execstate->rownum = 0;
@@ -2402,6 +2651,7 @@ static void
ogrEndForeignScan(ForeignScanState* node)
{
OgrFdwExecState* execstate = (OgrFdwExecState*) node->fdw_state;
+ elog(DEBUG3, "%s: entered function", __func__);
if (execstate)
{
elog(DEBUG2, "OGR FDW processed %d rows from OGR", execstate->rownum);
@@ -2442,13 +2692,10 @@ ogrGetFidColumn(const TupleDesc td)
int i;
for (i = 0; i < td->natts; i++)
{
-#if PG_VERSION_NUM >= 110000
- NameData attname = td->attrs[i].attname;
- Oid atttypeid = td->attrs[i].atttypid;
-#else
- NameData attname = td->attrs[i]->attname;
- Oid atttypeid = td->attrs[i]->atttypid;
-#endif
+ Form_pg_attribute att_tuple = TupleDescAttr(td, i);
+ NameData attname = att_tuple->attname;
+ Oid atttypeid = att_tuple->atttypid;
+
if ((atttypeid == INT4OID || atttypeid == INT8OID) &&
strcaseeq("fid", attname.data))
{
@@ -2467,6 +2714,7 @@ ogrGetFidColumn(const TupleDesc td)
* there could always be a virtual fid travelling with the queries,
* and the FDW table itself wouldn't need such a column?
*/
+
#if PG_VERSION_NUM >= 140000
static void
ogrAddForeignUpdateTargets(PlannerInfo* planinfo,
@@ -2475,14 +2723,37 @@ ogrAddForeignUpdateTargets(PlannerInfo* planinfo,
Relation target_relation)
{
Query* parsetree = planinfo->parse;
-#else
+ Form_pg_attribute att;
+ Var* var;
+ TupleDesc tupdesc = target_relation->rd_att;
+ int fid_column = ogrGetFidColumn(tupdesc);
+
+ elog(DEBUG3, "%s: entered function", __func__);
+
+ if (fid_column < 0)
+ {
+ elog(ERROR, "table '%s' does not have a 'fid' column", RelationGetRelationName(target_relation));
+ }
+
+ att = TupleDescAttr(tupdesc, fid_column);
+
+ /* Make a Var representing the desired value */
+ var = makeVar(parsetree->resultRelation,
+ att->attnum,
+ att->atttypid,
+ att->atttypmod,
+ att->attcollation,
+ 0);
+
+ add_row_identity_var(planinfo, var, rte_index, "fid");
+}
+#else /* PG_VERSION_NUM < 140000 */
static void
ogrAddForeignUpdateTargets(Query* parsetree,
RangeTblEntry* target_rte,
Relation target_relation)
{
-#endif
ListCell* cell;
Form_pg_attribute att;
Var* var;
@@ -2490,7 +2761,7 @@ ogrAddForeignUpdateTargets(Query* parsetree,
TupleDesc tupdesc = target_relation->rd_att;
int fid_column = ogrGetFidColumn(tupdesc);
- elog(DEBUG2, "ogrAddForeignUpdateTargets");
+ elog(DEBUG3, "%s: entered function", __func__);
if (fid_column < 0)
{
@@ -2523,10 +2794,10 @@ ogrAddForeignUpdateTargets(Query* parsetree,
TargetEntry* target = (TargetEntry*) lfirst(cell);
elog(DEBUG4, "parsetree->targetList %s:%d", target->resname, target->resno);
}
+}
+#endif
- return;
-}
/*
* ogrBeginForeignModify
@@ -2543,7 +2814,7 @@ ogrBeginForeignModify(ModifyTableState* mtstate,
Oid foreigntableid;
OgrFdwState* state;
- elog(DEBUG2, "ogrBeginForeignModify");
+ elog(DEBUG3, "%s: entered function", __func__);
foreigntableid = RelationGetRelid(rinfo->ri_RelationDesc);
state = getOgrFdwState(foreigntableid, OGR_MODIFY_STATE);
@@ -2577,6 +2848,9 @@ ogrExecForeignUpdate(EState* estate,
int64 fid;
OGRFeatureH feat;
OGRErr err;
+ Form_pg_attribute attrs;
+
+ elog(DEBUG3, "%s: entered function", __func__);
/* Is there a fid column? */
fid_column = ogrGetFidColumn(td);
@@ -2591,11 +2865,9 @@ ogrExecForeignUpdate(EState* estate,
/* What is the value of the FID for this record? */
fid_datum = slot->tts_values[fid_column];
-#if PG_VERSION_NUM >= 110000
- fid_type = td->attrs[fid_column].atttypid;
-#else
- fid_type = td->attrs[fid_column]->atttypid;
-#endif
+ attrs = TupleDescAttr(td, fid_column);
+ fid_type = attrs->atttypid;
+
if (fid_type == INT8OID)
{
fid = DatumGetInt64(fid_datum);
@@ -2750,7 +3022,7 @@ ogrExecForeignInsert(EState* estate,
OGRErr err;
GIntBig fid;
- elog(DEBUG2, "ogrExecForeignInsert");
+ elog(DEBUG3, "%s: entered function", __func__);
#if PG_VERSION_NUM >= 120000
/*
@@ -2812,6 +3084,9 @@ ogrExecForeignDelete(EState* estate,
Datum fid_datum;
int64 fid;
OGRErr err;
+ Form_pg_attribute attrs;
+
+ elog(DEBUG3, "%s: entered function", __func__);
/* Is there a fid column? */
fid_column = ogrGetFidColumn(td);
@@ -2826,11 +3101,8 @@ ogrExecForeignDelete(EState* estate,
/* What is the value of the FID for this record? */
fid_datum = planSlot->tts_values[fid_column];
-#if PG_VERSION_NUM >= 110000
- fid_type = td->attrs[fid_column].atttypid;
-#else
- fid_type = td->attrs[fid_column]->atttypid;
-#endif
+ attrs = TupleDescAttr(td, fid_column);
+ fid_type = attrs->atttypid;
if (fid_type == INT8OID)
{
@@ -2861,7 +3133,7 @@ ogrEndForeignModify(EState* estate, ResultRelInfo* rinfo)
{
OgrFdwModifyState* modstate = rinfo->ri_FdwState;
- elog(DEBUG2, "ogrEndForeignModify");
+ elog(DEBUG3, "%s: entered function", __func__);
ogrFinishConnection(&(modstate->ogr));
@@ -2877,7 +3149,7 @@ ogrIsForeignRelUpdatable(Relation rel)
OgrConnection ogr;
Oid foreigntableid = RelationGetRelid(rel);
- elog(DEBUG2, "ogrIsForeignRelUpdatable");
+ elog(DEBUG3, "%s: entered function", __func__);
/* Before we say "yes"... */
/* Does the foreign relation have a "fid" column? */
@@ -2895,7 +3167,7 @@ ogrIsForeignRelUpdatable(Relation rel)
/* Something in the open process set the readonly flags */
/* Perhaps user has manually set the foreign table option to readonly */
if (ogr.ds_updateable == OGR_UPDATEABLE_FALSE ||
- ogr.lyr_updateable == OGR_UPDATEABLE_FALSE)
+ ogr.lyr_updateable == OGR_UPDATEABLE_FALSE)
{
return readonly;
}
@@ -2944,6 +3216,8 @@ ogrImportForeignSchema(ImportForeignSchemaStmt* stmt, Oid serverOid)
char layer_name[STR_MAX_LEN];
char table_name[STR_MAX_LEN];
+ elog(DEBUG3, "%s: entered function", __func__);
+
/* Are we importing all layers in the OGR datasource? */
import_all = streq(stmt->remote_schema, "ogr_all");
=====================================
ogr_fdw.h
=====================================
@@ -22,8 +22,11 @@
#include "access/reloptions.h"
#include "access/sysattr.h"
#include "access/transam.h"
+#include "access/tupdesc.h"
+#include "catalog/indexing.h"
#include "catalog/namespace.h"
#include "catalog/pg_collation.h"
+#include "catalog/pg_extension.h"
#include "catalog/pg_foreign_table.h"
#include "catalog/pg_foreign_server.h"
#include "catalog/pg_namespace.h"
@@ -33,6 +36,7 @@
#include "commands/copy.h"
#include "commands/defrem.h"
#include "commands/explain.h"
+#include "commands/extension.h"
#include "commands/vacuum.h"
#include "foreign/fdwapi.h"
#include "foreign/foreign.h"
@@ -48,7 +52,9 @@
#include "parser/parsetree.h"
#include "storage/ipc.h"
#include "utils/builtins.h"
+#include "utils/catcache.h"
#include "utils/date.h"
+#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/numeric.h"
@@ -61,6 +67,11 @@
#include "optimizer/var.h"
#else
#include "executor/tuptable.h"
+#include "optimizer/appendinfo.h"
+#endif
+
+#ifdef PACKAGE_URL
+#undef PACKAGE_URL
#endif
/* GDAL/OGR includes and compat */
@@ -75,6 +86,8 @@
/* hexwkb is not. */
#define OGR_FDW_HEXWKB TRUE
+extern Oid GEOMETRYOID;
+
typedef enum
{
OGR_UNMATCHED,
@@ -93,22 +106,25 @@ typedef enum {
typedef struct OgrFdwColumn
{
/* PgSQL metadata */
- int pgattnum; /* PostgreSQL attribute number */
- int pgattisdropped; /* PostgreSQL attribute dropped? */
- char* pgname; /* PostgreSQL column name */
- Oid pgtype; /* PostgreSQL data type */
- int pgtypmod; /* PostgreSQL type modifier */
-
- /* For reading */
- Oid pginputfunc; /* PostgreSQL function to convert cstring to type */
+ int pgattnum; /* PgSQL attribute number */
+ int pgattisdropped; /* PgSQL attribute dropped? */
+ char* pgname; /* PgSQL column name */
+ Oid pgtype; /* PgQL data type */
+ int pgtypmod; /* PgSQL type modifier */
+
+ bool pgisarray;
+ Oid pgelmtype; /* If column is array then this is nonzero */
+
+ /* For reading. If array, for array element type. */
+ Oid pginputfunc; /* PgSQL convert cstring to type */
Oid pginputioparam;
- Oid pgrecvfunc; /* PostgreSQL function to convert binary to type */
+ Oid pgrecvfunc; /* PgSQL convert binary to type */
Oid pgrecvioparam;
- /* For writing */
- Oid pgoutputfunc; /* PostgreSQL function to convert type to cstring */
+ /* For writing. If array, for array element type. */
+ Oid pgoutputfunc; /* PgSQL convert type to cstring */
bool pgoutputvarlena;
- Oid pgsendfunc; /* PostgreSQL function to convert type to binary */
+ Oid pgsendfunc; /* PgSQL convert type to binary */
bool pgsendvarlena;
/* OGR metadata */
=====================================
ogr_fdw_common.c
=====================================
@@ -13,6 +13,7 @@
#include "stringbuffer.h"
#include "pg_config_manual.h"
+
/* Prototype for function that must be defined in PostgreSQL (it is) */
/* and in ogr_fdw_info (it is) */
const char * quote_identifier(const char *ident);
@@ -91,11 +92,15 @@ static void
ogrTypeToPgType(OGRFieldDefnH ogr_fld, char *pgtype, size_t width)
{
OGRFieldType ogr_type = OGR_Fld_GetType(ogr_fld);
+#if GDAL_VERSION_MAJOR >= 2
+ OGRFieldSubType ogr_subtype = OGR_Fld_GetSubType(ogr_fld);
+#endif
+
switch(ogr_type)
{
case OFTInteger:
#if GDAL_VERSION_MAJOR >= 2
- if( OGR_Fld_GetSubType(ogr_fld) == OFSTBoolean )
+ if (ogr_subtype == OFSTBoolean)
{
snprintf(pgtype, width, "boolean");
break;
@@ -109,8 +114,18 @@ ogrTypeToPgType(OGRFieldDefnH ogr_fld, char *pgtype, size_t width)
break;
case OFTString:
{
- int ogr_fld_width = OGR_Fld_GetWidth(ogr_fld);
- if (ogr_fld_width > 0)
+ int ogr_fld_width;
+#if GDAL_VERSION_MAJOR >= 2
+ if (ogr_subtype == OFSTJSON)
+ {
+ snprintf(pgtype, width, "jsonb");
+ break;
+ }
+#endif
+ ogr_fld_width = OGR_Fld_GetWidth(ogr_fld);
+
+ /* MaxAttrSize = 10*1024*1024 */
+ if (ogr_fld_width > 0 && ogr_fld_width < 10*1024*1024)
snprintf(pgtype, width, "varchar(%d)", ogr_fld_width);
else
snprintf(pgtype, width, "varchar");
@@ -141,6 +156,9 @@ ogrTypeToPgType(OGRFieldDefnH ogr_fld, char *pgtype, size_t width)
case OFTInteger64:
snprintf(pgtype, width, "bigint");
break;
+ case OFTInteger64List:
+ snprintf(pgtype, width, "bigint[]");
+ break;
#endif
default:
CPLError(CE_Failure, CPLE_AssertionFailed,
@@ -199,6 +217,7 @@ ogrGeomTypeToPgGeomType(stringbuffer_t *buf, OGRwkbGeometryType gtype)
#endif
case wkbNone:
CPLError(CE_Failure, CPLE_AssertionFailed, "Cannot handle OGR geometry type wkbNone");
+ break;
default:
CPLError(CE_Failure, CPLE_AssertionFailed, "Cannot handle OGR geometry type '%d'", gtype);
}
=====================================
ogr_fdw_func.c
=====================================
@@ -36,6 +36,7 @@ Datum ogr_fdw_drivers(PG_FUNCTION_ARGS)
bool elem_byval;
char elem_align;
int num_drivers;
+ int i;
if (GDALGetDriverCount() <= 0)
GDALAllRegister();
@@ -52,7 +53,6 @@ Datum ogr_fdw_drivers(PG_FUNCTION_ARGS)
arr_elems = palloc0(num_drivers * sizeof(Datum));
get_typlenbyvalalign(elem_type, &elem_len, &elem_byval, &elem_align);
- int i;
for (i = 0; i < num_drivers; i++) {
#if GDAL_VERSION_MAJOR <= 1
OGRSFDriverH hDriver = OGRGetDriver(i);
@@ -82,8 +82,7 @@ Datum ogr_fdw_version(PG_FUNCTION_ARGS)
const char *ogr_fdw_ver = OGR_FDW_RELEASE_NAME;
char ver_str[256];
snprintf(ver_str, sizeof(ver_str), "OGR_FDW=\"%s\" GDAL=\"%s\"", ogr_fdw_ver, gdal_ver);
- text* ver_txt = cstring_to_text(ver_str);
- PG_RETURN_TEXT_P(ver_txt);
+ PG_RETURN_TEXT_P(cstring_to_text(ver_str));
}
=====================================
ogr_fdw_info.c
=====================================
@@ -59,7 +59,7 @@ strip_spaces(char* str)
/* in the ogr_fdw_common module works */
const char* quote_identifier(const char* ident);
-char identifier[NAMEDATALEN+3];
+static char identifier[NAMEDATALEN+3];
const char*
quote_identifier(const char* ident)
@@ -76,7 +76,8 @@ quote_identifier(const char* ident)
}
return identifier;
}
-char config_options[STR_MAX_LEN] = {0};
+
+static char config_options[STR_MAX_LEN] = {0};
static void
@@ -133,9 +134,11 @@ usage()
" Show what input file formats are supported.\n"
"\n");
printf(
- "note (1): You can specify either -l (layer name) or -i (layer index) if you specify both -l will be used\n"
+ "note (1): You can specify either -l (layer name) or -i (layer index)\n"
+ " if you specify both -l will be used\n"
"note (2): config options are specified as a comma deliminated list without the OGR_<driver>_ prefix\n"
- "so OGR_XLSX_HEADERS = FORCE OGR_XLSX_FIELD_TYPES = STRING would become:\n\"HEADERS = FORCE,FIELD_TYPES = STRING\""
+ " so OGR_XLSX_HEADERS = FORCE OGR_XLSX_FIELD_TYPES = STRING would become:\n"
+ " \"HEADERS = FORCE,FIELD_TYPES = STRING\""
"\n");
exit(0);
}
@@ -155,7 +158,7 @@ main(int argc, char** argv)
usage();
}
- while ((ch = getopt(argc, argv, "h?s:l:f:t:n:i:o:")) != -1)
+ while ((ch = getopt(argc, argv, "hfs:l:t:n:i:o:")) != -1)
{
switch (ch)
{
=====================================
output/import.source
=====================================
@@ -84,13 +84,21 @@ ORDER BY c.ordinal_position;
person_s_value | character varying | {"column_name=person's value"}
(4 rows)
-SELECT * FROM imp3.no_geom_apost;
- fid | name | age | person_s_value
------+---------+-----+----------------
- 1 | Peter | 34 | 10.2
- 2 | John | 77 | 3.4
- 3 | Paul | 45 | 19.2
- 4 | Matthew | 35 | 18.2
-(4 rows)
+SELECT name,age FROM imp3.no_geom_apost WHERE name = 'Paul';
+ name | age
+------+-----
+ Paul | 45
+(1 row)
+
+SELECT name,age FROM imp3.no_geom_apost WHERE name IS NULL;
+ name | age
+------+-----
+(0 rows)
+
+SELECT name,age FROM imp3.no_geom_apost WHERE name = '';
+ name | age
+------+-----
+ | 44
+(1 row)
------------------------------------------------
=====================================
output/pgsql.source
=====================================
@@ -16,12 +16,10 @@ CREATE TABLE bytea_local (
);
----------------------------------------------------------------------
-- Populate local table
-INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
- VALUES ('Jim', '14232'::bytea, 23, 1, 4.3, 5.5, '2010-10-10'::date, '13:23:21'::time, '2010-10-10 13:23:21'::timestamp, 'this', 'y' );
-INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
- VALUES ('Marvin', '55555'::bytea, 34, 2, 5.4, 10.13, '2011-11-11'::date, '15:21:45'::time, '2011-11-11 15:21:45'::timestamp, 'that', 'n' );
-INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
- VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
+INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn) VALUES
+ ('Jim', '14232'::bytea, 23, 1, 4.3, 5.5, '2010-10-10'::date, '13:23:21'::time, '2010-10-10 13:23:21'::timestamp, 'this', 'y' ),
+ ('Marvin', '55555'::bytea, 34, 2, 5.4, 10.13, '2011-11-11'::date, '15:21:45'::time, '2011-11-11 15:21:45'::timestamp, 'that', 'n' ),
+ (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
----------------------------------------------------------------------
-- Create remote table
CREATE SERVER pgserver
@@ -191,4 +189,49 @@ SELECT a.fid, a.name, b.name
3 | |
(3 rows)
-
+----------------------------------------------------------------------
+-- Populate local array table
+SET client_min_messages = NOTICE;
+CREATE TABLE array_local (
+ fid integer primary key,
+ geom bytea,
+ txt text[],
+ int int2[],
+ flt float4[],
+ b boolean[]
+);
+INSERT INTO array_local (fid,txt, int, flt, b) VALUES
+ (1, ARRAY['Jim'], ARRAY[1,2,3], ARRAY[3.4,5.6,7.8], ARRAY[true,false]),
+ (2, ARRAY['Jim',NULL,'Joe'], ARRAY[1,3,NULL,4], ARRAY[4.5,NULL,3.4], ARRAY[false,NULL]),
+ (3, NULL, NULL, NULL, NULL);
+----------------------------------------------------------------------
+-- Create remote array table
+CREATE FOREIGN TABLE array_fdw (
+ fid bigint,
+ geom bytea,
+ txt text[],
+ int int4[],
+ flt float8[],
+ b boolean[]
+) SERVER pgserver OPTIONS (layer 'array_local');
+SELECT fid, txt, int, flt, b FROM array_fdw;
+ fid | txt | int | flt | b
+-----+--------------+-----------+---------------+-------
+ 1 | {Jim} | {1,2,3} | {3.4,5.6,7.8} | {t,f}
+ 2 | {Jim,"",Joe} | {1,3,0,4} | {4.5,0,3.4} | {f,f}
+ 3 | | | |
+(3 rows)
+
+----------------------------------------------------------------------
+-- Update remote array table
+UPDATE array_fdw SET
+ txt = ARRAY['newJim', 'newJoe'],
+ int = ARRAY[-2, -1, 0, 1, 2],
+ flt = ARRAY[-0.1, 0.0, 0.1]
+WHERE fid = 3;
+SELECT txt, int, flt FROM array_fdw WHERE fid = 3;
+ txt | int | flt
+-----------------+---------------+--------------
+ {newJim,newJoe} | {-2,-1,0,1,2} | {-0.1,0,0.1}
+(1 row)
+
=====================================
stringbuffer.c
=====================================
@@ -11,6 +11,11 @@
#include "stringbuffer.h"
+#pragma GCC diagnostic push
+#ifndef __clang__
+#pragma GCC diagnostic ignored "-Wsuggest-attribute=format"
+#endif
+
/**
* Allocate a new stringbuffer_t. Use stringbuffer_destroy to free.
*/
View it on GitLab: https://salsa.debian.org/debian-gis-team/pgsql-ogr-fdw/-/compare/ba0244dc6d8ad50284761cce0bba57ef894aafe2...695e6e7f5d80a88b6655412f614e90b327dbb312
--
View it on GitLab: https://salsa.debian.org/debian-gis-team/pgsql-ogr-fdw/-/compare/ba0244dc6d8ad50284761cce0bba57ef894aafe2...695e6e7f5d80a88b6655412f614e90b327dbb312
You're receiving this email because of your account on salsa.debian.org.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://alioth-lists.debian.net/pipermail/pkg-grass-devel/attachments/20250521/54cae0a8/attachment-0001.htm>
More information about the Pkg-grass-devel
mailing list