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.YYYY 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.YYYY > 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 >