[GIS] Geoserver SQL view parameterizing

geoserverpostgresql

I've got a postgresql table that has the following attributes:

name        String
begintime   String
endtime     String
the_geom    Point

Now I'd like to create an sql view in geoserver (2.2 snapshot). In short, i only want to map the features where begintime equals a certain param. Looking at the geoserver documentation and the questions here at the forum, I came up with this:

select name, begintime, endtime, the_geom 
from n80_tellingen_merge 
where begintime = %utct%

This gets me the following error:

ERROR: syntax error at or near ")" Position: 111

So basically I'm asking two things:

  • What am I doing wrong?
  • Since I don't have brackets or more than 111 positions, where can I find the actual query sent to postgresql? Can I consult a log somewhere? (Ubuntu server 13.04)

My apolagies if I'm missing something obvious.

Best Answer

I'm not sure exactly what you want to return from your query. If you want an exact match to a string then you need to wrap the string in single quotes, i.e.

select name, begintime, endtime, the_geom 
from n80_tellingen_merge 
where begintime = '%utct%'

This will return all records where beingtime is exactly %utct%.

If you want to return records which contains the string 'utct' then you need to use the like parameter:

select name, begintime, endtime, the_geom 
from n80_tellingen_merge 
where begintime like '%utct%'

See the PostgreSQL docs for more details on pattern matching.

Related Question