It seems your "key" field must have type integer and not text.
I reproduced your problem with the following snippet:
uri = QgsDataSourceURI()
uri.setConnection("localhost", "5432", "mydb", "user", "pass")
query="SELECT id, CAST (id as text)|| '_' || (ST_DumpPoints(geom)).path[2] as key, (ST_DumpPoints(geom)).geom as points FROM line"
uri.setDataSource("", "(" + query + ")", "points", "", "key")
vl = iface.addVectorLayer(uri.uri(), "QueryLayer", "postgres")
Only after I changed the "key" field type to "int", I could get the layer loaded into QGIS:
uri = QgsDataSourceURI()
uri.setConnection("localhost", "5432", "mydb", "user", "pass")
query="SELECT id, CAST(CAST (id as text)|| (ST_DumpPoints(geom)).path[2] as int) as key, (ST_DumpPoints(geom)).geom as points FROM line"
uri.setDataSource("", "(" + query + ")", "points", "", "key")
vl = iface.addVectorLayer(uri.uri(), "QueryLayer", "postgres")
Note that I had to adjust your gid to id and your the_geom to geom, in order to work with my own data.
There's probably an issue with escaping double quotes in your final command. To bypass that you could create a view in your Postgres database and give it a simple name. Something like:
CREATE VIEW v_export AS
SELECT * FROM planning."dm_planning-apps"
WHERE "dm_planning-apps"."Modified_Reference" ILIKE '%np%';
Then from the command line you could use
"C:\Program Files\PostgreSQL\9.1\bin\pgsql2shp.exe" -f "D:\path\shapefile.shp" -h localhost -p 5432 -u postgres -P xxxx mapdata v_export
It's kind of a cheat, but it should work.
Best Answer
PostGIS 2.0 doesn't have the function zmflag(geometry). It is now known as st_zmflag(geometry). I'm betting that the version of pgsql2shp is from a prior version of PostGIS. You can check with
At the top of the output, it should list the release version. Something like...