1

I can't find a solution for this problem after a couple of hours of research. The query is:

SELECT searchaddress('some string');
FETCH ALL IN mycursor;

The function searchaddress returns a REFCURSOR, named mycursor. In PostgreSQL using FETCH I get results. Using the exact same query (no parameters) in a Geoserver SQL View I get an error:

ERROR: syntax error at or near ";" Position: 73

If you need more information I will provide.

Edit: One more piece of information. The query used to be:

SELECT searchaddress('some string');
FETCH ALL IN "<unnamed cursor 1>";

Under this format it was anyone's guess what was the number of "unnamed cursor i". But the SQL View did return a response and not an error when I managed to guess the number of the cursor. Because I don't know how to guess the number I had the function searchaddress changed so it would return a cursor named "mycursor". At that point the querry stoped working altogether under Geosever.

  • 1
    Can you create a view in postgis using that function? – Ian Turton Jan 13 '15 at 13:22
  • Good question! Being new to SQL I am not even sure how to approach this problem. With the knowledge I have I can't do it. Should I understand that it can't be done or is there a way? – user4071485 Jan 13 '15 at 13:45
3

The problem is that SQL view in geoserver doesn't allow to use semicolon(';') in SQL statement.

Geoserver only allows one query statement. You didn't provide the structure of Your database, but may be it's possible to build SELECT query with subquery and get the same result.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.