Combining validation into main query
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
If you use EXISTS in the WHERE clause, you should come closer to what you
want realised.
For further help it would make it easier if you shared the whole queries
and perhaps a more detailed description of the goal you are trying to
accomplish.
HTH
Kind regards/met vriendelijke groet,
Serge Fonville
Convince Microsoft!
They need to add TRUNCATE PARTITION in SQL Server
https://connect.microsoft.com/SQLServer/feedback/details/417926/truncate-partition-of-partitioned-table
2013/1/13 Robert James <srobertjames@gmail.com>
Show quoted text
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 badUPDATE_PRODUCTS(username, password, productid, newval) -- Updates
product, Returns 0 if good, -1 if username bad, -2 if password bad, -3
if productid bad--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 badUPDATE_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.klaver@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general