Re: Trying to execute several queries involving temp tables in a PHP script - Mailing list pgsql-general

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