SQL query works in PostgreSQL but not in GeoServer SQL View

Question

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.

• Ian Turton: Can you create a view in postgis using that function?
• user4071485: 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?

Answer 1

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.