Re: [GENERAL] Trying to execute several queries involving temp tables in a PHP script

2012-06-15 Thread Alexander Farber 
Thank you Misa, the without-temp-tables query has worked flawlessly.


On Wed, Jun 13, 2012 at 5:01 PM, Misa Simic  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

2012-06-13 Thread Misa Simic 
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 

>  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

2012-06-13 Thread Leif Biberg Kristensen 
 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

2012-06-13 Thread Misa Simic 
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 

> 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

2012-06-13 Thread Alban Hertroys 
On 13 June 2012 15:12, Alexander Farber  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

[GENERAL] Trying to execute several queries involving temp tables in a PHP script

2012-06-13 Thread Alexander Farber 
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