Geoserver sql view using an external sql query?

Question

I am trying to figure out a way of producing a view of a layer in GeoServer, using an external SQL query. By external I mean, for instance, a user defined query in a PHP from where all the parameters defined there would act as variables in the query. By doing so, I would allow users of my web mapping application to select only what they intended to see. Is this possible? I am using PostgreSQL/PostGIS + GeoServer + OpenLayers and the front end webpage was developed using PHP.

EDIT 1 - I've been able to create the parametric view and pass a parameter value to the viewparams call successfully. However, when trying to pass a parameter as result of a variable value the layer polygons just wont load. I'm declaring a variable as var data = 10000000; and passing the variable to the viewparams as follows: viewparams:'volume:data'. The wms layer polygons wont load, but the getfeatureinfo works as if the viewparams wasn't defined because when i click on the map, i get the featureinfo pop-up with the corresponding info. Any ideas? Image showing the debug

Answer 1

Starting with GeoServer 2.1.0, layers can also be defined as SQL Views. SQL Views allow executing a custom SQL query on each request to the layer. This avoids the need to create a database view for complex queries.

Even more usefully, SQL View queries can be parameterized via string substitution. Parameter values can be supplied in both WMS and WFS requests. Default values can be supplied for parameters, and input values can be validated by Regular Expressions to eliminate the risk of SQL injection attacks.

See Here for the full documentation.

Please note warning regarding SQL Injection Attacks.

• fgbrandao: First of all, thanks for your reply. For what i understand, i'll have to use parametric sql views. This means that a user defines a query and what he defines in that specific query is passed as parameters to the sql view, using viewparams request, right? Thanks again!
• Mark Cupitt: You are welcome, if it answers your question, feel free to up-vote and marking as the Correct answer by clicking on the Tick Symbol
• Mark Cupitt: Yes, you do use Parametric Views. You create the Layer in GeoServer as a SQL View, then set up the parameters on the Layer View, then pass the parameters in as part of your WMS Call from your client by adding the viewparams parameter to the WMS GetMap
• fgbrandao: Tanks Mark, once again. I'll give it a shot! If i get in trouble, i'll stop by again!