I am trying to recreate the functionality of a query found inside of a mapfile on our mapserver into a plpgsql stored procedure.
Here is the query:
geom from (select g.gid, g.geom, g.basin, a.\"DATE\", a.\"VALUE\" from sarffg_basins_00_regional_3sec as g join \"%prod_table%\" as a on g.basin = a.\"BASIN\" where a.\"DATE\" = '%prod_date%') as subquery using unique gid using srid=4326
Within my stored procedure, I have:
RETURN QUERY
EXECUTE 'SELECT geom FROM (
SELECT g.gid,
g.geom,
g.basin,
a.date,
a.value
FROM sarffg_basins_00_regional_3sec AS g
JOIN '||tablename_ts||' AS a
ON g.basin = a.basin
WHERE a.date = '''||adj_timestamp||''')
AS subquery USING UNIQUE gid USING srid=4326';
The above query found within my mapfile works fine. When I try calling my stored procedure inside of psql, I get:
ERROR: syntax error at or near "USING"
LINE 11: AS subquery USING UNIQUE gid USING srid=4326
^
QUERY: SELECT geom FROM (
SELECT g.gid,
g.geom,
g.basin,
a.date,
a.value
FROM sarffg_basins_00_regional_3sec AS g
JOIN temp_table_ts AS a
ON g.basin = a.basin
WHERE a.date = '2017-01-15 00:00:00+00')
AS subquery USING UNIQUE gid USING srid=4326
CONTEXT: PL/pgSQL function ingest_ffgs_prod_composite_csv(text,bigint,boolean,boolean) line 239 at RETURN QUERY
I have also tried omitting the "using" clause within my function and instead leaving that part within the mapfile after my stored procedure is called, i.e.:
DATA "select * from ingest_ffgs_prod_composite_csv('%prod_table%', 1484438400) as subquery using unique gid using srid=4326"
With the stored procedure containing:
RETURN QUERY
EXECUTE 'SELECT geom FROM (
SELECT g.gid,
g.geom,
g.basin,
a.date,
a.value
FROM sarffg_basins_00_regional_3sec AS g
JOIN '||tablename_ts||' AS a
ON g.basin = a.basin
WHERE a.date = '''||adj_timestamp||''');
But this leaves me with the error in my mapserver error log:
[Wed Jan 25 02:28:17 2017].593733 msDrawMap(): Image handling error. Failed to draw layer named 'regional_basin_values'.
[Wed Jan 25 02:28:17 2017].659656 msPostGISLayerWhichShapes(): Query error. Error executing query: ERROR: syntax error at or near "select"
LINE 1: ..._BASIN_TIMESERIES', 1484438400) as subquery where select * &...
^
[Wed Jan 25 02:28:17 2017].659862 msDrawMap(): Image handling error. Failed to draw layer named 'regional_basin_product'.
[Wed Jan 25 02:28:22 2017].836950 msPostGISLayerWhichShapes(): Query error. Error executing query: ERROR: syntax error at or near "select"
LINE 1: ..._BASIN_TIMESERIES', 1484438400) as subquery where select * &...
Finally, I tried leaving the front part of the query within the mapfile and only turning the subquery into the stored procedure:
mapfile:
DATA "geom from (select * from ingest_ffgs_prod_composite_csv('%prod_table%', 1484438400)) as subquery using unique gid using srid=4326"
stored procedure:
RETURN QUERY
EXECUTE 'SELECT g.gid,
g.geom,
g.basin,
a.date,
a.value
FROM sarffg_basins_00_regional_3sec AS g
JOIN '||tablename_ts||' AS a
ON g.basin = a.basin
WHERE a.date = '''||adj_timestamp||''');
And this leaves me with:
[Wed Jan 25 02:35:36 2017].527302 msDrawMap(): Image handling error. Failed to draw layer named 'regional_basin_values'.
[Wed Jan 25 02:35:36 2017].617289 msPostGISLayerWhichShapes(): Query error. Error executing query: ERROR: column "VALUE" does not exist
LINE 1: select "VALUE",encode(ST_AsBinary(ST_Force2D("geom"),'NDR'),...
^
[Wed Jan 25 02:35:36 2017].617511 msDrawMap(): Image handling error. Failed to draw layer named 'regional_basin_product'.
[Wed Jan 25 02:35:42 2017].103566 msPostGISLayerWhichShapes(): Query error. Error executing query: ERROR: column "VALUE" does not exist
LINE 1: select "VALUE",encode(ST_AsBinary(ST_Force2D("geom"),'NDR'),...
The return statement being executed here is:
RETURN QUERY
EXECUTE 'SELECT g.'||quote_ident('gid')||',
g.'||quote_ident('geom')||',
g.'||quote_ident('basin')||',
a.'||quote_ident('DATE')||',
a.'||quote_ident('VALUE')||'
FROM sarffg_basins_00_regional_3sec AS g JOIN '||quote_ident(prod_table)||' AS a
ON g.'||quote_ident('basin')||' = a.'||quote_ident('BASIN')||'
WHERE a.'||quote_ident('DATE')||' = '''||adj_timestamp||'''';
I have verified that prod_table has a column called "VALUE", so I'm not sure why I would be seeing this error. It is also important to note that calling my procedure from within psql yields no errors.
(I have two very similar return statements because my code queries a table with capital column names, and in the absence of that table it creates one from a CSV that doesn't have the capital names.)
Also not sure if it's relevant but here is what my function returns:
RETURNS table (
gid integer,
geom geometry(MultiPolygon,4326),
basin double precision,
date timestamptz,
value double precision
)
Any help would be greatly appreciated
I guess, you use the field VALUE in a filter or something similar in the mapfile (hard to say for sure without mapfile). This filter must expect capitalized column names and this is why the original query had also capitalized column names:
select g.gid, g.geom, g.basin, a.\"DATE\", a.\"VALUE\" from....
If so, you only have to capitalize the columns returned by your procedure:
RETURNS table (
gid integer,
geom geometry(MultiPolygon,4326),
basin double precision,
"DATE" timestamptz,
"VALUE" double precision
)
Remember that in PostgreSql the case of column and table names matter if you surround then with double quote. This query:
SELECT VALUE from ...
is case independent, while this one:
SELECT "VALUE" from ...
really requires a table with capitalized column names. And tables with capitalized column names require double quote:
CREATE TABLE test ("VALUE" text, .....