Re: [GENERAL] Trying to execute several queries involving temp tables in a PHP script
Thank you Misa, the without-temp-tables query has worked flawlessly. On Wed, Jun 13, 2012 at 5:01 PM, Misa Simic misa.si...@gmail.com wrote: I think you can have all in one query, without temp tables: SELECT r.rid, r.cards, to_char(r.stamp, 'DD.MM. HH24:MI') as day, c.bid, c.trix, c.pos, c.money, c.last_ip, c.quit, u.id, u.first_name, u.avatar, u.female, u.city, u.vip CURRENT_DATE as vip FROM pref_rounds r, pref_cards c, pref_users u WHERE u.id = c.id and r.rid = c.rid and r.rid in ( select rid from pref_cards where stamp now() - interval '1 day' and id in ( select id from pref_money where yw = to_char(current_timestamp - interval '1 week', 'IYYY-IW') order by money desc limit 10 ) and bid = 'Misere' and trix 0 ) order by rid, pos; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trying to execute several queries involving temp tables in a PHP script
On 13 June 2012 15:12, Alexander Farber alexander.far...@gmail.com wrote: And when I split my statements into multiple prepare()/execute() or query() calls, then the temp. tables aren't found anymore. Did you remember to wrap them in a transaction like you did in your prepared statement? -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trying to execute several queries involving temp tables in a PHP script
Hi Alexander, I think you can have all in one query, without temp tables: SELECT r.rid, r.cards, to_char(r.stamp, 'DD.MM. HH24:MI') as day, c.bid, c.trix, c.pos, c.money, c.last_ip, c.quit, u.id, u.first_name, u.avatar, u.female, u.city, u.vip CURRENT_DATE as vip FROM pref_rounds r, pref_cards c, pref_users u WHERE u.id = c.id and r.rid = c.rid and r.rid in ( select rid from pref_cards where stamp now() - interval '1 day' and id in ( select id from pref_money where yw = to_char(current_timestamp - interval '1 week', 'IYYY-IW') order by money desc limit 10 ) and bid = 'Misere' and trix 0 ) order by rid, pos; 2012/6/13 Alexander Farber alexander.far...@gmail.com Hello fello PostgreSQL users, with PHP 5.3.3 and PostgreSQL 8.4.11 (and a pgbouncer, but I've tried without it too) I'm trying to execute several SQL queries with 2 temp tables (listed below) and then use the result of a final join to construct a JSON array. Unfortunately my script using prepare/execute (and I've tried query() too) fails with PHP error: cannot insert multiple commands into a prepared statement. And when I split my statements into multiple prepare()/execute() or query() calls, then the temp. tables aren't found anymore. Any ideas please on how to handle this situation in PHP scripts, do I really have to encapsulate my calls into a pl/PgSQL function? More details on my query and setup: http://stackoverflow.com/questions/11010784/error-cannot-insert-multiple-commands-into-a-prepared-statement And below is my PHP code: try { $options = array(PDO::ATTR_ERRMODE = PDO::ERRMODE_EXCEPTION); $db = new PDO(sprintf('pgsql:host=%s port=%u; dbname=%s', DBHOST, DBPORT, DBNAME), DBUSER, DBPASS, $options); $sth = $db-prepare( start transaction; create temporary table temp_ids (id varchar not null) on commit drop; insert into temp_ids (id) select id from pref_money where yw = to_char(current_timestamp - interval '1 week', 'IYYY-IW') order by money desc limit 10; create temporary table temp_rids (rid integer not null) on commit drop; insert into temp_rids (rid) select rid from pref_cards where stamp now() - interval '1 day' and id in (select id from temp_ids) and bid = 'Misere' and trix 0; SELECT r.rid, r.cards, to_char(r.stamp, 'DD.MM. HH24:MI') as day, c.bid, c.trix, c.pos, c.money, c.last_ip, c.quit, u.id, u.first_name, u.avatar, u.female, u.city, u.vip CURRENT_DATE as vip FROM pref_rounds r, pref_cards c, pref_users u WHERE u.id = c.id and r.rid = c.rid and r.rid in (select rid from temp_rids) order by rid, pos; commit; ); $sth-execute(); while ($row = $sth-fetch(PDO::FETCH_ASSOC)) { # construct a JSON array of objects } } catch (Exception $e) { exit('Database problem: ' . $e-getMessage()); } Thank you Alex -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trying to execute several queries involving temp tables in a PHP script
Onsdag 13. juni 2012 15.12.33 skrev Alexander Farber : Any ideas please on how to handle this situation in PHP scripts, do I really have to encapsulate my calls into a pl/PgSQL function? I believe that Misa Simic's idea that you can do it all in a single query without temp tables is correct. But anyway, it's always a good idea to encapsulate multiple interdependent queries in a single pl/pgsql function. I tend to keep my PHP code as simple as possible, and do most of the work inside the database. regards, Leif http://code.google.com/p/yggdrasil-genealogy/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trying to execute several queries involving temp tables in a PHP script
I agree with approach to have all in functions... In that case there would not be a problem with temp tables because of inside 1 transaction they would work... suggestion was just to solve problem from php... what would be achiavable just trough 1 query, or to use PDO and then: $dbh-beginTransaction(); $dbh-exec(query1); $dbh-exec(query2); $dbh-exec(query3); $dbh-commit(); Kind Regards, Misa 2012/6/13 Leif Biberg Kristensen l...@solumslekt.org Onsdag 13. juni 2012 15.12.33 skrev Alexander Farber : Any ideas please on how to handle this situation in PHP scripts, do I really have to encapsulate my calls into a pl/PgSQL function? I believe that Misa Simic's idea that you can do it all in a single query without temp tables is correct. But anyway, it's always a good idea to encapsulate multiple interdependent queries in a single pl/pgsql function. I tend to keep my PHP code as simple as possible, and do most of the work inside the database. regards, Leif http://code.google.com/p/yggdrasil-genealogy/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general