GeoServer parametric SQL view. ERROR: function key(boolean) does not exist

Question

I have been had a task: to allow a single WFS request to return two different responses depending on zoom: return usual

SELECT * FROM table

if zoom level >n, and return

SELECT ST_SimplifyPreserveTopology(geom, x) FROM table

if zoom level <=n.

I tried to do it by this definition like in advice from there:

SELECT wkb_geometry, ogc_fid, key
  (CASE 
  WHEN %zoom_lvl% > CAST ('8' as smallint) 
    THEN wkb_geometry=ST_SimplifyPreserveTopology(wkb_geometry, 49::double precision)
  WHEN %zoom_lvl% <= CAST ('8' as smallint) 
    THEN wkb_geometry = wkb_geometry
END)

from input.admin_centers_and_districts

But when I try to do it I face this

ERROR: function key(boolean) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 46

What is wrong? cast('8'smallint) is useless.

Answer 1

PostGIS is saying it can't find a function called key that takes a boolean operator.

SELECT wkb_geometry, ogc_fid, key
  (CASE 
  WHEN %zoom_lvl% > CAST ('8' as smallint) 
    THEN wkb_geometry=ST_SimplifyPreserveTopology(wkb_geometry, 49::double precision)
  WHEN %zoom_lvl% <= CAST ('8' as smallint) 
    THEN wkb_geometry = wkb_geometry
END)

from input.admin_centers_and_districts

This is because this statement evaluates to be something like:

SELECT wkb_geometry, ogc_fid, key
  true
from input.admin_centers_and_districts

Which is nonsense. SQL might look like a programming language but it isn't really one. So you want something like

SELECT ogc_fid, key,
  (CASE 
  WHEN %zoom_lvl% > 8) 
    THEN ST_SimplifyPreserveTopology(wkb_geometry, 49::double precision)
  else 
    wkb_geometry
END) as wkb_geometry

from input.admin_centers_and_districts

Answer 2

In SQL select, you're supposed to describre the result you want, not what to do. Here you put a lot of assignement =, in SQL you use it as comparison (equivalent to == in many languages) except in a few cases like update. If you want to rename the column of your result, you write as new_name at the end of the column description. I suppose that's what you wanted to do, because the way you wrote it you have key(CASE...END) which is interpreted as a function called with a parameter boolean because you compare something in your CASE with = (not found so -> error). Here is an exemple, simplified, and without = inside your CASE:

SELECT 
    wkb_geometry, 
    ogc_fid, 
    (
        CASE 
            WHEN %zoom_lvl% > 8 THEN ST_SimplifyPreserveTopology(wkb_geometry, 49)
            ELSE wkb_geometry
        END
    ) as key
FROM input.admin_centers_and_districts