Opened 3 years ago

Closed 3 years ago

#5018 closed defect (fixed)

pgsql2shp doesn't work with CTE queries (aka WITH)

Reported by: robe Owned by: robe
Priority: medium Milestone: PostGIS 3.1.5
Component: utils/loader-dumper Version: 3.1.x
Keywords: Cc:

Description

https://lists.osgeo.org/pipermail/postgis-users/2021-November/045047.html

From the mailing list:

>  > $ pgsql2shp ... "WITH foo AS (SELECT 2) SELECT 1"
>  > Table WITH foo AS (SELECT 2) SELECT 1 does not exist

I found a work-around:

> $ pgsql2shp ... "SELECT * FROM (WITH foo AS (SELECT 2) SELECT 1) bar"
> Initializing... 
> Done (postgis major version: 3).
> Output shape: Null Shape
> Dumping: X [1 rows].

The issue is this line:

https://git.osgeo.org/gitea/postgis/postgis/src/branch/master/loader/pgsql2shp-cli.c#L140

Which explicitly looks for a SELECT to determine whether to treat the input as a table or query.

Change History (2)

comment:1 by Regina Obe <lr@…>, 3 years ago

In 540e03f/git:

Support for CTEs in pgsql2shp. References #5018 for PostGIS 3.2.0. Also minor news cleanup

comment:2 by Regina Obe <lr@…>, 3 years ago

Resolution: fixed
Status: newclosed

In 77a4e86/git:

Support for CTEs in pgsql2shp. Closes #5018 for PostGIS 3.1.5. Also minor news cleanup

Note: See TracTickets for help on using tickets.