Geoserver SQL View that uses an existing fuction

Question

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.

• Derek: 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.
• nmtoken: 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.
• Derek: 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.

Answer 1

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.

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