0

I am attempting to create a Geoserver SQL View layer that uses an existing function in my Postgres/Postgis database.

Starting with the simplest case I have successfully created a layer using the following SQL statement on the Edit SQL View page:

SELECT geom FROM map.parcel WHERE pkid = %pkid%

Then I create a function in the database:

CREATE OR REPLACE FUNCTION map.gs_parcel (_pkid integer)
RETURNS TABLE (geom public.geometry) AS
$body$
BEGIN

RETURN QUERY
SELECT p.geom FROM map.parcel p WHERE pkid = _pkid;

END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

And I try to create a SQL View using:

SELECT geom FROM map.gs_parcel(%pkid%)

But I get the error:

ERROR: function map.gs_parcel(integer) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 34

What am I doing wrong? I need to expand this simple example to take multiple parameters and add logic, which I can only do using pl/pgsql.

  • Sorry, I was suffering from cross-server confusion. I created the Postgres function on my local development server and was tring to access it from GeoServer running on our production server. This part of the problem solved. – Derek Sep 13 '16 at 9:57
  • When you say that This part of the problem solved, does that mean that you still have a problem? If so can you edit your question to provide clarification. – nmtoken Sep 14 '16 at 17:12
  • What I meant was I have a lot more work to do and don't know yet if there will be more "problems". If there are I will open new specific questions to address them. – Derek Sep 16 '16 at 9:24
2

You probably created the SQL View in a datastore without access to your map schema.
By default, the schema of a new postgis datastore is public.
You can change it to map or remove it.

  • I have a single PostGIS JNDI datastore with "map" as the schema. – Derek Sep 13 '16 at 9:42
  • Did you have a valid (i.e integer) default value for %pkid% parameter ? – WKT Sep 13 '16 at 9:50
  • Yes. Thank you for the help. My bad, solution below. – Derek Sep 13 '16 at 9:58

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.