Thread: temp tables
hi,
im working with php 4 and postgresql 8 and in my php script id like to create a temp table on my database server. how do i do this? how do i verify it was created?
i tried the following:
$sql = "create temp table s_info(a int, b text) on commit delete rows ";
pg_query($dbh,$sql);
$dbh is my connection.
any help will be highly appreciated.
On Apr 28, 2005, at 12:45 AM, Cima wrote: > > im working with php 4 and postgresql 8 and in my php script id like to > create a temp table on my database server. how do i do this? how do i > verify it was created? > > i tried the following: > > $sql = "create temp table s_info(a int, b text) on commit delete > rows "; > > pg_query($dbh,$sql); > > > $dbh is my connection. > > any help will be highly appreciated. You can verify it was created by checking the result from pg_query and looking at pg_last_error. The table is automatically dropped at the end of the connection, most likely when your PHP script ends. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
Is this safe? What happens if multiple users hit the PHP page nearly simultaneously (or at least just after someone else)and the temp table exists? I avoided the temp table approach in an app for exactly this reason... What does PG doin this case? Do the temp tables belong to the session/connection and they're insulated from each other? Thanks, Bret > -----Original Message----- > From: pgsql-novice-owner@postgresql.org > [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of John DeSoi > Sent: Thursday, April 28, 2005 8:18 AM > To: Cima > Cc: PostgreSQL Novice > Subject: Re: [NOVICE] temp tables > > > On Apr 28, 2005, at 12:45 AM, Cima wrote: > > > > > im working with php 4 and postgresql 8 and in my php script > id like to > > create a temp table on my database server. how do i do > this? how do i > > verify it was created? > > > > i tried the following: > > > > $sql = "create temp table s_info(a int, b text) on commit > delete rows > > "; > > > > pg_query($dbh,$sql); > > > > > > $dbh is my connection. > > > > any help will be highly appreciated. > > > You can verify it was created by checking the result from > pg_query and looking at pg_last_error. The table is > automatically dropped at the end of the connection, most > likely when your PHP script ends. > > > John DeSoi, Ph.D. > http://pgedit.com/ > Power Tools for PostgreSQL > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so > that your > message can get through to the mailing list cleanly >
On 4/28/05, Schuhmacher, Bret <Bret.Schuhmacher@aspect.com> wrote:See http://www.postgresql.org/docs/8.0/interactive/sql-createtable.html#SQL-CREATETABLE-COMPATIBILITY
From the docs
"
Is this safe? What happens if multiple users hit the PHP page nearly simultaneously (or at least just after someone else) and the temp table exists? I avoided the temp table approach in an app for exactly this reason... What does PG do in this case? Do the temp tables belong to the session/connection and they're insulated from each other?
Thanks,
Bret
> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto: pgsql-novice-owner@postgresql.org] On Behalf Of John DeSoi
> Sent: Thursday, April 28, 2005 8:18 AM
> To: Cima
> Cc: PostgreSQL Novice
> Subject: Re: [NOVICE] temp tables
>
>
> On Apr 28, 2005, at 12:45 AM, Cima wrote:
>
> >
> > im working with php 4 and postgresql 8 and in my php script
> id like to
> > create a temp table on my database server. how do i do
> this? how do i
> > verify it was created?
> >
> > i tried the following:
> >
> > $sql = "create temp table s_info(a int, b text) on commit
> delete rows
> > ";
> >
> > pg_query($dbh,$sql);
> >
> >
> > $dbh is my connection.
> >
> > any help will be highly appreciated.
>
>
> You can verify it was created by checking the result from
> pg_query and looking at pg_last_error. The table is
> automatically dropped at the end of the connection, most
> likely when your PHP script ends.
>
>
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so
> that your
> message can get through to the mailing list cleanly
>
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
From the docs
"
Compatibility
The CREATE TABLE command conforms to SQL-92 and to a subset of SQL:1999, with exceptions listed below.
Temporary Tables
Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL standard, the effect is not the same. In the standard, temporary tables are defined just once and automatically exist (starting with empty contents) in every session that needs them. PostgreSQL instead requires each session to issue its own CREATE TEMPORARY TABLE command for each temporary table to be used. This allows different sessions to use the same temporary table name for different purposes, whereas the standard's approach constrains all instances of a given temporary table name to have the same table structure.
The standard's definition of the behavior of temporary tables is widely ignored. PostgreSQL's behavior on this point is similar to that of several other SQL databases.
The standard's distinction between global and local temporary tables is not in PostgreSQL, since that distinction depends on the concept of modules, which PostgreSQL does not have. For compatibility's sake, PostgreSQL will accept the GLOBAL and LOCAL keywords in a temporary table declaration, but they have no effect.
The ON COMMIT clause for temporary tables also resembles the SQL standard, but has some differences. If the ON COMMIT clause is omitted, SQL specifies that the default behavior is ON COMMIT DELETE ROWS. However, the default behavior in PostgreSQL is ON COMMIT PRESERVE ROWS. The ON COMMIT DROP option does not exist in SQL."
On Wed, April 27, 2005 9:45 pm, Cima said: > im working with php 4 and postgresql 8 and in my php script id like to > create a temp table on my database server. how do i do this? how do i > verify it was created? > > i tried the following: > > $sql = "create temp table s_info(a int, b text) on commit delete rows "; > > pg_query($dbh,$sql); > > > $dbh is my connection. Did it print an error message? What was the return value of pg_query()? Did you check http://php.net/pg_last_error What happens if you then do: $sql = "insert into s_info(a, b) values(1, 'Test')"; pg_query($dbh, $sql) or die(pg_last_error()); $sql = "select * from s_info"; $sinfo = pg_query($dbh, $sql) or die(pg_last_error()); $srow = 0; while (list($a, $b) = @pg_fetch_row($sinfo, $srow++)){ echo "a: $a b: $b<br />\n"; } What makes you think it didn't work? -- Like Music? http://l-i-e.com/artists.htm
Cima wrote: > hi, > > im working with php 4 and postgresql 8 and in my php script id like to create a temp table on my database server. how doi do this? how do i verify it was created? > Well if the creation fails pg_query will throw an error. However temp tables are only good for the life of the connection (script in this case). You will probably want to consider that. Sincerely, Joshua D. Drake > i tried the following: > > $sql = "create temp table s_info(a int, b text) on commit delete rows "; > > pg_query($dbh,$sql); > > > $dbh is my connection. > > any help will be highly appreciated. -- Your PostgreSQL solutions provider, Command Prompt, Inc. 24x7 support - 1.800.492.2240, programming, and consulting Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit http://www.commandprompt.com / http://www.postgresql.org