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.

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.