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