我试图将在我们的地图服务器上的地图文件内部找到的查询的功能重新创建为plpgsql存储过程.
I am trying to recreate the functionality of a query found inside of a mapfile on our mapserver into a plpgsql stored procedure.
这是查询:
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在存储过程中，我有:
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';在我的mapfile中找到的上述查询工作正常.当我尝试在psql中调用存储过程时，得到:
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我还尝试了在函数中省略"using"子句，而是在调用存储过程后将该部分保留在mapfile中，即:
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"存储过程包含:
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||''');但是这给我留下了mapserver错误日志中的错误:
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 * &...最后，我尝试将查询的前部保留在mapfile中，只将子查询变成存储过程:
Finally, I tried leaving the front part of the query within the mapfile and only turning the subquery into the stored procedure:
映射文件:
DATA "geom from (select * from ingest_ffgs_prod_composite_csv('%prod_table%', 1484438400)) as subquery using unique gid using srid=4326"存储过程:
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||''');这给我留下了
[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'),...此处执行的return语句为:
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||'''';我已验证prod_table的列名为"VALUE"，所以我不确定为什么会看到此错误.同样重要的是要注意，从psql内调用我的过程不会产生任何错误.
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.
(我有两个非常相似的return语句，因为我的代码用大写的列名查询一个表，而在没有该表的情况下，它从没有大写名称的CSV中创建一个表.)
(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 )任何帮助将不胜感激
推荐答案我猜想，您在过滤器中使用了VALUE字段，或者在映射文件中使用了类似内容(如果没有映射文件，很难确定). 此过滤器必须使用大写的列名，这就是为什么原始查询也使用大写的列名的原因:
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 )请记住，在PostgreSql中，如果用双引号引起来，则列名和表名的大小写很重要. 此查询:
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, .....更多推荐
在“使用中"或附近出现语法错误.
发布评论