postgresql 错误：列索引超出范围：1，列数：0
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27480846/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ERROR : The column index is out of range: 1, number of columns: 0
提问by user3129056
I'm using wso2dss 3.0.0.I'm trying to execute a postgresql query i.e.
我正在使用 wso2dss 3.0.0。我正在尝试执行 postgresql 查询，即
SELECT addressid, geocode
FROM maddress
WHERE geocode::point <@ circle '((18.9750,72.8258), 5)';
It is working fine in PostgreSQL.When i'm using same query in wso2dss i.e.
它在 PostgreSQL 中工作正常。当我在 wso2dss 中使用相同的查询时，即
SELECT addressid, geocode
FROM maddress
WHERE geocode::point <@ circle '((?,?), ?)';
It gives me error like :
它给了我这样的错误：
DS Fault Message: Error in 'SQLQuery.processNormalQuery'
DS Code: DATABASE_ERROR
Source Data Service:-
Name: Geofence_DataService
Location: /Geofence_DataService.dbs
Description: N/A
Default Namespace: http://ws.wso2.org/dataservice
Current Request Name: adding_geofence_op
Current Params: {longitude=72.8258, radius=4, latitude=18.9750}
Nested Exception:-
DS Fault Message: Error in 'createProcessedPreparedStatement'
DS Code: UNKNOWN_ERROR
Nested Exception:-
org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
if i remove " ' "(quotation mark) of circle then also it will not execute. query '' look like this :
如果我删除" ' "（引号）的圆，那么它也不会执行。查询 '' 看起来像这样：
SELECT addressid, geocode FROM maddress WHERE geocode::point <@ circle ((?,?), ?);
SELECT addressid, geocode FROM maddress WHERE geocode::point <@ circle ((?,?), ?);
it'll give following error :
它会给出以下错误：
Caused by: javax.xml.stream.XMLStreamException: DS Fault Message: Error in 'SQLQuery.processNormalQuery'
DS Code: DATABASE_ERROR
Source Data Service:-
Name: Geofence_DataService
Location: /Geofence_DataService.dbs
Description: N/A
Default Namespace: http://ws.wso2.org/dataservice
Current Request Name: geofence_op
Current Params: {longitude=72.8258, radius=4, latitude=18.9750}
Nested Exception:-
org.postgresql.util.PSQLException: ERROR: function circle(record, double precision) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type cast
But circle is inbuilt geographical function of PostgreSQL then is it necessary to write explicit function? else where is the exact error? Even if i put the query with input parameter as i execute in PostgreSQL then also it's working.If so then why it is not accepting dynamic parameters? Please let me know..
但是circle是PostgreSQL内置的地理函数，那么有必要写显式函数吗？否则确切的错误在哪里？即使我在 PostgreSQL 中执行时将查询与输入参数放在一起，它也可以工作。如果是这样，那么为什么它不接受动态参数？请告诉我..
采纳答案by Erwin Brandstetter
Geometric typescan be input in multiple ways.
几何类型可以通过多种方式输入。
In the first form, your
?parameters are not replaced with values because they are literal parts of a string. So 0 parameters are expected ...In the second form without single quotes, your
?parameters are replaced, but((18.9750,72.8258), 5)is interpreted to be a row type, which doesn't work withcircle().
在第一种形式中，您的
?参数不会替换为值，因为它们是字符串的文字部分。所以预计0个参数......在没有单引号的第二种形式中，您的
?参数被替换，但((18.9750,72.8258), 5)被解释为行类型，它不适用于circle().
You are trying to invoke the function circle()that takes a pointand a double precision("center and radius to circle"). These are valid syntax variants:
您正在尝试调用circle()带有 apoint和 a的函数double precision（“圆心和半径”）。这些是有效的语法变体：
SELECT circle '((18.9750,72.8258), 5)' AS cast_literal
' <(18.9750,72.82580),5>'::circle AS cast_literal2
, circle(point '(18.9750,72.8258)', '5') AS literal_point_n_radius
, circle(point(18.9750,72.8258), '5') AS point_n_literal_radius
, circle(point(18.9750,72.8258), 5) AS point_n_radius
SQL fiddle.
The cast to ::textis just to sanitize the deranged display in SQL fiddle
SQL 小提琴。
强制转换::text只是为了清理 SQL 小提琴中的混乱显示
In your case, to provide numeric values(not a string literal), use the last form and it should work:
在您的情况下，要提供数值（不是字符串文字），请使用最后一种形式，它应该可以工作：
SELECT addressid, geocode
FROM maddress
WHERE geocode::point <@ circle(point(?,?), ?);
If wso2dss (which I have no experience with) does not accept functions, you have to use one of the first two forms and provide a singleparameter as string literal:
如果 wso2dss（我没有经验）不接受函数，则必须使用前两种形式之一并提供单个参数作为字符串文字：
SELECT addressid, geocode
FROM maddress
WHERE geocode::point <@ circle ?;
... where the parameter is the concatenated literal as displayed above.
... 其中参数是如上所示的连接文字。
You couldlet Postgres do the concatenation and still pass three numeric values:
您可以让 Postgres 进行连接并仍然传递三个数值：
SELECT addressid, geocode
FROM maddress
WHERE geocode::point <@ ('(('::text || ? || ',' || ? || '),' || ? || ')')::circle;
