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.
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.