Re: Combining validation into main query

On 01/12/2013 04:58 PM, Robert James wrote:
> Typically, my web application does some initial validation, then, if
> it passes, does the actual query. For both performance and
> simplicity, I'd like to combine these all into one trip to Postgres.
> Ideally, I'd like to do this in SQL. If that's not possible, I could
> use PL/pgsql, though I'm not adept at it.
>
> Example #1:
> qry = "SELECT 1 FROM users WHERE username = ? AND PASSWORD = ?"
> if qry.count > 0
> qry = "SELECT ..."
>
> Example #2:
> qry = "SELECT 1 FROM users WHERE username = ? AND PASSWORD = ?"
> if qry.count > 0
> qry = "SELECT product WHERE ..."
> if qry.count > 0
> qry = "UPDATE product SET..."
>
> How can I combine these into one Postgres call? I'd like it to look
> something like:
>
> GET_PRODUCTS(username, password) -- Returns products, or -1 if
> username bad, -2 if password is bad
>
> UPDATE_PRODUCTS(username, password, productid, newval) -- Updates
> product, Returns 0 if good, -1 if username bad, -2 if password bad, -3
> if productid bad

So where are you running the above functions?
I would say you are going to end up using some procedural language to do
what you want. PL/pgsql is not that difficult to learn. If you end up
creating the functions on the server using plpgsql then you get your
simple query:

select get_products(username, password);

>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com