1

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.

3
  • 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, 2016 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, 2016 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, 2016 at 9:24

1 Answer 1

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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