Thread: Create View from command line

On 5/10/2010 4:43 AM, OisinJK wrote:
> Hi
>
> I’m trying to build a Windows Form application that needs to alter the
> definition of a View, depending on user input/selection. Essentially,
> the user is selecting a property of known coordinates (‘x’,’y’) and
> setting a ‘buffer’ distance . I then want to retrieve the records which
> represent the map features within the specified distance of the
> property. The WHERE clause of my view is:
>
> st_dwithin(mytable.geom, st_setsrid(st_point(x, y), 27700), buffer);
>
> I’m thinking this could be achieved either by making x, y and buffer
> parameters or, I simply ALTER the View statement with literal values.
> Whichever, I need to do this from a command line attached to a windows
> form event, e.g. button_click.
>
> I cannot work out how to do this. Can anyone point me in the right
> direction. I’ve looked at psql.exe, but can’t work out if this holds the
> answer.
>
> Thanks
>
> Oisin
>


Changing the view on the fly is a bad idea.  Multiple users would step
all over themselves.

I see two options:

1) don't do the where inside the view.  Have the view return the column
and have the client generate the where. so client would run:

select * from view where st_dwithin(geom, st_setsrid(st_point(x, y),
27700), buffer);

2) convert it to a stored procedure, which can take arguments, then have
the client run something like:

select * from mapFeat(42, 42, 27700)



-Andy