From pawel@astercity.net Mon Jan  11 22:48:47 1999
Received: from astercity.net (root@astercity.net [195.116.8.78])
	by hub.org (8.9.1/8.9.1) with ESMTP id RAA09941
	for <pgsql-sql@postgreSQL.org>; Mon, 11 Jan 1999 17:48:47 -0500 (EST)
	(envelope-from pawel@astercity.net)
Received: from mail.astercity.net (verona58.waw.pdi.net [195.116.8.58])
	by astercity.net (8.9.2/8.9.2) with SMTP id XAA00213
	for <pgsql-sql@postgreSQL.org>; Mon, 11 Jan 1999 23:48:09 +0100 (EET)
Date: Mon, 11 Jan 1999 23:39:11 +0100
From: Pawel Pierscionek <pawel@astercity.net>
X-Mailer: The Bat! (v1.19) S/N 359CE73E
Reply-To: Pawel Pierscionek <pawel@astercity.net>
Organization: PDi- Publiczny Dostep do Internetu
X-Priority: 3 (Normal)
Message-ID: <19985.990111@astercity.net>
To: pgsql-sql <pgsql-sql@postgreSQL.org>
Subject: Temporary tables
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi,

I have to play a lot with temp tables.
Could you pls, give me a hint on emulating them under Postgres.

I have to use them quite often and with 10000 rows each.
I  don't use indices cause I only cache a search result and don't need
any sorting, searching on temptables.

What's faster:

1)
once:
 create tmptable;

many times:
 delete from tmptable;
 insert into tmptable select ... ;

or

2)
many times:
drop tmptable;
select ... into tmptable;

I  prefer  the  second way since I have a lot of concurrent access and
create unique named tables under each transaction.
Unfortunately the DROP thing dumps a core under PLPGSQL function. :(

TIA,
Pawel





From tiberiu@akela.ro Mon Jun  28 16:09:19 1999
Received: from ns.canad.ro (IDENT:root@ns.canad.ro [193.230.177.1])
	by hub.org (8.9.3/8.9.3) with ESMTP id MAA25736
	for <pgsql-sql@hub.org>; Mon, 28 Jun 1999 12:09:19 -0400 (EDT)
	(envelope-from tiberiu@akela.ro)
Received: from linux.akela.ro (root@ns.akela.ro [193.226.47.105])
	by ns.canad.ro (8.9.3/8.9.3) with ESMTP id TAA25061
	for <pgsql-sql@hub.org>; Mon, 28 Jun 1999 19:08:47 +0300
Received: from tibi (tibi.akela.ro [192.168.1.2])
	by linux.akela.ro (8.9.1/8.9.1) with SMTP id TAA11772
	for <pgsql-sql@hub.org>; Mon, 28 Jun 1999 19:08:18 -0200
Message-ID: <00f501bec180$28c18b60$0201a8c0@tibi.akela.ro>
From: "Tiberiu Craciun" <tiberiu@akela.ro>
To: <pgsql-sql@hub.org>
Subject: Temporary tables
Date: Mon, 28 Jun 1999 19:06:19 +0300
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_00F2_01BEC199.4DEEB840"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3

This is a multi-part message in MIME format.

------=_NextPart_000_00F2_01BEC199.4DEEB840
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

How can I use temporary tables in PostgresSQL 6.5 ?
=20
Thanks


------=_NextPart_000_00F2_01BEC199.4DEEB840
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">
<HTML>
<HEAD>

<META content=3Dtext/html;charset=3Diso-8859-1 =
http-equiv=3DContent-Type>
<META content=3D'"MSHTML 4.72.3110.7"' name=3DGENERATOR>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV>
<DIV><FONT color=3D#000000 size=3D2>How can I use temporary tables in =
PostgresSQL=20
6.5 ?</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT color=3D#000000 size=3D2>Thanks</FONT></DIV>
<DIV>&nbsp;</DIV></DIV></BODY></HTML>

------=_NextPart_000_00F2_01BEC199.4DEEB840--




From maillist@candle.pha.pa.us Mon Jun  28 19:03:59 1999
Received: from candle.pha.pa.us (root@s5-03.ppp.op.net [209.152.195.67])
	by hub.org (8.9.3/8.9.3) with ESMTP id PAA51492
	for <pgsql-sql@hub.org>; Mon, 28 Jun 1999 15:03:59 -0400 (EDT)
	(envelope-from maillist@candle.pha.pa.us)
Received: (from maillist@localhost)
	by candle.pha.pa.us (8.9.0/8.9.0) id OAA21708;
	Mon, 28 Jun 1999 14:45:57 -0400 (EDT)
From: Bruce Momjian <maillist@candle.pha.pa.us>
Message-Id: <199906281845.OAA21708@candle.pha.pa.us>
Subject: Re: [SQL] Temporary tables
In-Reply-To: <00f501bec180$28c18b60$0201a8c0@tibi.akela.ro> from Tiberiu
	Craciun at "Jun 28, 1999 07:06:19 pm"
To: Tiberiu Craciun <tiberiu@akela.ro>
Date: Mon, 28 Jun 1999 14:45:57 -0400 (EDT)
CC: pgsql-sql@hub.org
X-Mailer: ELM [version 2.4ME+ PL56 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> How can I use temporary tables in PostgresSQL 6.5 ?
>  
> Thanks
> 

	CREATE TEMP TABLE xxx(x int);

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



From michael_shelton@non.hp.com Thu Jan  10 23:02:32 2002
Received: from palrel13.hp.com (palrel13.hp.com [156.153.255.238])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id g0AN2WB58851
	for <pgsql-sql@postgresql.org>; Thu, 10 Jan 2002 18:02:32 -0500 (EST)
	(envelope-from michael_shelton@non.hp.com)
Received: from xparelay2.corp.hp.com (xparelay2.corp.hp.com [15.58.137.112])
	by palrel13.hp.com (Postfix) with ESMTP id B2671E000F2
	for <pgsql-sql@postgresql.org>; Thu, 10 Jan 2002 15:02:34 -0800 (PST)
Received: from xpabh1.corp.hp.com (xpabh1.corp.hp.com [15.58.136.191])
	by xparelay2.corp.hp.com (Postfix) with ESMTP id 400AC4000E2
	for <pgsql-sql@postgresql.org>; Thu, 10 Jan 2002 15:01:58 -0800 (PST)
Received: by xpabh1.corp.hp.com with Internet Mail Service (5.5.2653.19)
	id <CL85K4MA>; Thu, 10 Jan 2002 15:02:34 -0800
Message-ID: <FB60DFB2C0E24449AC0C21F743B935410150F91F@xboi02.boi.hp.com>
From: "SHELTON,MICHAEL (Non-HP-Boise,ex1)" <michael_shelton@non.hp.com>
To: pgsql-sql@postgresql.org
Subject: Temporary tables
Date: Thu, 10 Jan 2002 15:02:33 -0800
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2653.19)
Content-Type: text/plain;
	charset="iso-8859-1"
X-Archive-Number: 200201/80
X-Sequence-Number: 6170

Hello,

I am attempting to optimize a number of queries that are dependant on a
first query (big join) that is expensive to run.  Instead of having the join
be executed for each subsequent query I would like to put the results of the
first join into a temp table.  I found syntax for creating a temp table in
the idocs (SELECT <foo> INTO <tmp_table> FROM <table>) and this works fine.
My problem is that the table isn't very "temporary".  Is there a way to have
the table drop automagically when I'm done with it.  I'm accessing it
through PHP 4.0 via apache on a 7.1.3 db.  I would really like it to drop
when I close the connection.  

A 2nd question is what about concurrency?  Is there a way to hide this temp
table from other concurrent connections to the db (I thought I remember
reading an earlier post about this) so that you don't get name conflicts
with the temp table?

Thanks,

Mike Shelton



From peter@memeticsystems.com Fri Jan  11 17:17:44 2002
Received: from mail.memeticsystems.com (flywheel.memeticsystems.com
	[64.85.80.85])
	by postgresql.org (8.11.3/8.11.4) with SMTP id g0BHHiB59336
	for <pgsql-sql@postgresql.org>; Fri, 11 Jan 2002 12:17:44 -0500 (EST)
	(envelope-from peter@memeticsystems.com)
Received: (qmail 25952 invoked from network); 11 Jan 2002 17:17:49 -0000
Received: from unknown (HELO PETER) (63.249.24.21)
	by flywheel.memeticsystems.com with SMTP; 11 Jan 2002 17:17:49 -0000
From: "Peter T. Brown" <peter@memeticsystems.com>
To: "'SHELTON,MICHAEL \(Non-HP-Boise,ex1\)'" <michael_shelton@non.hp.com>,
	<pgsql-sql@postgresql.org>
Subject: Re: Temporary tables
Date: Fri, 11 Jan 2002 09:17:48 -0800
Message-ID: <002101c19ac3$e4d50330$7d00000a@PETER>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2911.0)
In-Reply-To: <FB60DFB2C0E24449AC0C21F743B935410150F91F@xboi02.boi.hp.com>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Importance: Normal
X-Archive-Number: 200201/91
X-Sequence-Number: 6180

Look at the syntax for CREATE TABLE AS

You can CREATE TEMP TABLE <tablename> AS SELECT ...

This temp table will be removed automagically

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of SHELTON,MICHAEL
(Non-HP-Boise,ex1)
Sent: Thursday, January 10, 2002 3:03 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Temporary tables


Hello,

I am attempting to optimize a number of queries that are dependant on a
first query (big join) that is expensive to run.  Instead of having the join
be executed for each subsequent query I would like to put the results of the
first join into a temp table.  I found syntax for creating a temp table in
the idocs (SELECT <foo> INTO <tmp_table> FROM <table>) and this works fine.
My problem is that the table isn't very "temporary".  Is there a way to have
the table drop automagically when I'm done with it.  I'm accessing it
through PHP 4.0 via apache on a 7.1.3 db.  I would really like it to drop
when I close the connection.

A 2nd question is what about concurrency?  Is there a way to hide this temp
table from other concurrent connections to the db (I thought I remember
reading an earlier post about this) so that you don't get name conflicts
with the temp table?

Thanks,

Mike Shelton

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)




From marie.tuite@edisonaffiliates.com Tue Jun  11 23:04:44 2002
Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id EC869475A1A
	for <pgsql-sql@postgresql.org>; Tue, 11 Jun 2002 19:04:44 -0400 (EDT)
Received: from edisonaffiliates.com (w205.z208176060.chi-il.dsl.cnc.net
	[208.176.60.205])
	by postgresql.org (Postfix) with SMTP id C49F747596F
	for <pgsql-sql@postgresql.org>; Tue, 11 Jun 2002 19:04:43 -0400 (EDT)
Received: from redhen ([208.176.60.194]) by edisonaffiliates.com ;
	Tue, 11 Jun 2002 18:03:33 -0500
Reply-To: <marie.tuite@edisonaffiliates.com>
From: "Marie G. Tuite" <marie.tuite@edisonaffiliates.com>
To: <pgsql-sql@postgresql.org>
Subject: temporary tables
Date: Tue, 11 Jun 2002 18:07:16 -0500
Message-ID: <IGELKLINGDMODABPOOFEGEFNCDAA.marie.tuite@edisonaffiliates.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0)
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Importance: Normal
X-Archive-Number: 200206/137
X-Sequence-Number: 8102

Hey there,

Does anybody know where temporary table info is stored for the duration of
the temp table session?

Thanks.

Marie






From pgman@candle.pha.pa.us Tue Jun  11 23:34:26 2002
Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id E3A5947666D
	for <pgsql-sql@postgresql.org>; Tue, 11 Jun 2002 19:34:26 -0400 (EDT)
Received: from candle.pha.pa.us (216-55-132-35.dsl.san-diego.abac.net
	[216.55.132.35])
	by postgresql.org (Postfix) with ESMTP id B943F4760AB
	for <pgsql-sql@postgresql.org>; Tue, 11 Jun 2002 19:33:56 -0400 (EDT)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.11.6/8.10.1) id g5BNXmW28590;
	Tue, 11 Jun 2002 19:33:48 -0400 (EDT)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200206112333.g5BNXmW28590@candle.pha.pa.us>
Subject: Re: temporary tables
In-Reply-To: <IGELKLINGDMODABPOOFEGEFNCDAA.marie.tuite@edisonaffiliates.com>
To: marie.tuite@edisonaffiliates.com
Date: Tue, 11 Jun 2002 19:33:48 -0400 (EDT)
Cc: pgsql-sql@postgresql.org
X-Mailer: ELM [version 2.4ME+ PL97 (25)]
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII
X-Archive-Number: 200206/138
X-Sequence-Number: 8103

Marie G. Tuite wrote:
> Hey there,
> 
> Does anybody know where temporary table info is stored for the duration of
> the temp table session?

The mapping is kept in a per-backend memory area.  You can't really
access the mapping, but in 7.3, it will be in a temp schema which will
be visible, I think.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



From jinujosein@yahoo.com Sat Sep  27 13:32:07 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 8A665D1B50A
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Sep 2003 13:32:07 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 21479-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Sep 2003 10:31:40 -0300 (ADT)
Received: from web14911.mail.yahoo.com (web14911.mail.yahoo.com
	[216.136.225.249])
	by svr1.postgresql.org (Postfix) with SMTP id D4950D1B501
	for <pgsql-sql@postgresql.org>; Sat, 27 Sep 2003 10:31:35 -0300 (ADT)
Message-ID: <20030927133139.42953.qmail@web14911.mail.yahoo.com>
Received: from [202.9.168.99] by web14911.mail.yahoo.com via HTTP;
	Sat, 27 Sep 2003 06:31:39 PDT
Date: Sat, 27 Sep 2003 06:31:39 -0700 (PDT)
From: "George A.J" <jinujosein@yahoo.com>
Subject: Temporary tables
To: pgsql-sql@postgresql.org
In-Reply-To: <E1A2Mum-0005F0-1f@noon.pghoster.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1127839853-1064669499=:42630"
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200309/348
X-Sequence-Number: 15326

--0-1127839853-1064669499=:42630
Content-Type: text/plain; charset=us-ascii


hi,

I am using postgresql 7.3.2. Is there any function to determine 
whether a table exists in the database.Or is there any function 
that returns the current temp schema.
I am using a pl/pgsql function that create and drop a temporary table.
The procedure run correctly for the first time for each database connection. 
If I run the same procedure second time in the same connection it produces the error

"ERROR:  pg_class_aclcheck: relation 219389 not found
WARNING:  Error occurred while executing PL/pgSQL function testFun
WARNING:  line 20 at SQL statement "

Here is the function ....

---------------------------------------------------------
CREATE OR REPLACE FUNCTION testFun( varchar(10) ) RETURNS setof int
AS
'
DECLARE
     --Aliases for parameters
     vSBAcNo ALIAS FOR $1;
     --local variables
     vRow RECORD;
     
BEGIN
     -- create a tempory table to hold the numbers
     CREATE TABLE tempTable
     (
          testNo int
     ) ;
     
    for vRow IN select Entryno from  EntryTable LOOP
        
 return next vRow.Entryno;
       
        insert into tempTable values( vRow.Entryno);
                       
    end loop;

    drop table tempTable;

    return;
     
END;'

LANGUAGE 'plpgsql';

-------------------------------------------------------------

If i commented the "insert into tempTable values( vRow.Entryno);" line
the function works correctly. The problem is the oid of tempTable is kept when 
the function is first executed. the next execution creates another table with 
different oid. So the insert fails. 

I want to check whether the temporary table exist. If exist do not create the 
temporary table in subsequent calls and do not dorp it. This will solve the problem.

When i searched the pg_class i found the temp table name more than once. 
ie, a temporary table is created for each connection.I cannot distingush 
the temp tables. But the tables are in different schema.
Is there a method to get the current temporary schema? How postgres distinguish
this temp tables?.Is there a way to distinguish temporary tables.
The entries in pg_class table is same except the schema.
When i used the current_schema() function it returns public.

There is a lot of functions that uses temporary tables. I think that there is 
an option when creating temp tables in postgres 7.4 . But no way to use 7.4 
now it is a working database.

can i write a function to check the existance of the temporary table...
please help...

jinujose


---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
--0-1127839853-1064669499=:42630
Content-Type: text/html; charset=us-ascii

<P>hi,</P>
<P>I am using postgresql 7.3.2. Is there any function to determine <BR>whether a table exists in the database.Or is there any function <BR>that returns the current temp schema.<BR>I am using a pl/pgsql function that create and drop a temporary table.<BR>The procedure run correctly for the first time for each database connection. <BR>If I run the same procedure second time in the same connection it produces the error</P>
<P>"ERROR:&nbsp; pg_class_aclcheck: relation 219389 not found<BR>WARNING:&nbsp; Error occurred while executing PL/pgSQL function testFun<BR>WARNING:&nbsp; line 20 at SQL statement "</P>
<P>Here is the function ....</P>
<P>---------------------------------------------------------<BR>CREATE OR REPLACE FUNCTION testFun( varchar(10) ) RETURNS setof int<BR>AS<BR>'<BR>DECLARE<BR>&nbsp;&nbsp;&nbsp;&nbsp; --Aliases for parameters<BR>&nbsp;&nbsp;&nbsp;&nbsp; vSBAcNo ALIAS FOR $1;<BR>&nbsp;&nbsp;&nbsp;&nbsp; --local variables<BR>&nbsp;&nbsp;&nbsp;&nbsp; vRow RECORD;<BR>&nbsp;&nbsp;&nbsp;&nbsp; <BR>BEGIN<BR>&nbsp;&nbsp;&nbsp;&nbsp; -- create a tempory table to hold the numbers<BR>&nbsp;&nbsp;&nbsp;&nbsp; CREATE TABLE tempTable<BR>&nbsp;&nbsp;&nbsp;&nbsp; (<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; testNo int<BR>&nbsp;&nbsp;&nbsp;&nbsp; ) ;<BR>&nbsp;&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp; for vRow IN select Entryno from&nbsp; EntryTable LOOP<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR>&nbsp;return next vRow.Entryno;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; insert into tempTable values(
 vRow.Entryno);<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp; end loop;</P>
<P>&nbsp;&nbsp;&nbsp; drop table tempTable;</P>
<P>&nbsp;&nbsp;&nbsp; return;<BR>&nbsp;&nbsp;&nbsp;&nbsp; <BR>END;'</P>
<P>LANGUAGE 'plpgsql';</P>
<P>-------------------------------------------------------------</P>
<P>If i commented the "insert into tempTable values( vRow.Entryno);" line<BR>the function works correctly. The problem is the oid of tempTable is kept when <BR>the function is first executed. the next execution creates another table with <BR>different oid. So the insert fails. </P>
<P>I want to check whether the temporary table exist. If exist do not create the <BR>temporary table in subsequent calls and do not dorp it. This will solve the problem.</P>
<P>When i searched the pg_class i found the temp table name more than once. <BR>ie, a temporary table is created for each connection.I cannot distingush <BR>the temp tables. But the tables are in different schema.<BR>Is there a method to get the current temporary schema? How postgres distinguish<BR>this temp tables?.Is there a way to distinguish temporary tables.<BR>The entries in pg_class table is same except the schema.<BR>When i used the current_schema() function it returns public.</P>
<P>There is a lot of functions that uses temporary tables. I think that there is <BR>an option when creating temp tables in postgres 7.4 . But no way to use 7.4 <BR>now it is a working database.</P>
<P>can i write a function to check the existance of the temporary table...<BR>please help...</P>
<P>jinujose</P><p><hr SIZE=1>
Do you Yahoo!?<br>
<a href="http://shopping.yahoo.com/?__yltc=s%3A150000443%2Cd%3A22708228%2Cslk%3Atext%2Csec%3Amail">The New Yahoo! Shopping</a> - with improved product search
--0-1127839853-1064669499=:42630--



From dev@archonet.com Sat Sep  27 16:21:49 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 739A5D1B527
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Sep 2003 16:21:49 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 47806-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Sep 2003 13:21:39 -0300 (ADT)
Received: from anchor-post-33.mail.demon.net (anchor-post-33.mail.demon.net
	[194.217.242.91])
	by svr1.postgresql.org (Postfix) with ESMTP id 457B2D1B503
	for <pgsql-sql@postgresql.org>; Sat, 27 Sep 2003 13:21:38 -0300 (ADT)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-33.mail.demon.net with esmtp (Exim 3.35 #1)
	id 1A3HpB-000FqF-0X; Sat, 27 Sep 2003 17:21:37 +0100
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with ESMTP
	id C589F179D9; Sat, 27 Sep 2003 17:21:36 +0100 (BST)
Received: from client17.archonet.com (client17.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP
	id 1D691179D8; Sat, 27 Sep 2003 17:21:36 +0100 (BST)
From: Richard Huxton <dev@archonet.com>
To: "George A.J" <jinujosein@yahoo.com>, pgsql-sql@postgresql.org
Subject: Re: Temporary tables
Date: Sat, 27 Sep 2003 17:21:35 +0100
User-Agent: KMail/1.5
References: <20030927133139.42953.qmail@web14911.mail.yahoo.com>
In-Reply-To: <20030927133139.42953.qmail@web14911.mail.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200309271721.35419.dev@archonet.com>
X-Virus-Scanned: by AMaViS snapshot-20020531
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200309/349
X-Sequence-Number: 15327

On Saturday 27 September 2003 14:31, George A.J wrote:
> hi,
>
> I am using postgresql 7.3.2. Is there any function to determine
> whether a table exists in the database.Or is there any function
> that returns the current temp schema.
> I am using a pl/pgsql function that create and drop a temporary table.
> The procedure run correctly for the first time for each database
> connection. If I run the same procedure second time in the same connection
> it produces the error
>
> "ERROR:  pg_class_aclcheck: relation 219389 not found
> WARNING:  Error occurred while executing PL/pgSQL function testFun
> WARNING:  line 20 at SQL statement "

This is because plpgsql is "compiled" and so the reference to tempTable gets 
fixed the first time it is called. In your case, the oid was 219389.

Now, the second time you call the function, the temp table gets re-created, 
gets a new OID and the old reference is no longer valid your insert line.

There are two solutions:
1. Use pltcl/plperl or some other interpreted language that doesn't compile in 
table references.
2. Build your insert statement using EXECUTE ''INSERT INTO tempTable ''...

This second passes the query string into the parser, so it works just fine for 
your example. I think some of this is covered in the manuals, you can 
certainly find plenty on it in the archives.

-- 
  Richard Huxton
  Archonet Ltd



From tgl@sss.pgh.pa.us Sat Sep  27 17:09:36 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 49BF7D1B512
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Sep 2003 17:09:36 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 56934-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 Sep 2003 14:09:26 -0300 (ADT)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by svr1.postgresql.org (Postfix) with ESMTP id AC08ED1B4E6
	for <pgsql-sql@postgresql.org>; Sat, 27 Sep 2003 14:09:25 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id h8RH9P6c006295;
	Sat, 27 Sep 2003 13:09:26 -0400 (EDT)
To: "George A.J" <jinujosein@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Temporary tables 
In-reply-to: <20030927133139.42953.qmail@web14911.mail.yahoo.com> 
References: <20030927133139.42953.qmail@web14911.mail.yahoo.com>
Comments: In-reply-to "George A.J" <jinujosein@yahoo.com>
	message dated "Sat, 27 Sep 2003 06:31:39 -0700"
Date: Sat, 27 Sep 2003 13:09:25 -0400
Message-ID: <6294.1064682565@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200309/350
X-Sequence-Number: 15328

"George A.J" <jinujosein@yahoo.com> writes:
> When i searched the pg_class i found the temp table name more than once. 
> ie, a temporary table is created for each connection.I cannot distingush 
> the temp tables. But the tables are in different schema.
> Is there a method to get the current temporary schema?

Not directly, but you could try something like

	perform * from pg_class where
	  relname = 'mytable' and pg_table_is_visible(oid);
	if not found then ...  -- create the table

The visibility test would not succeed for temp tables belonging to other
backends.  (If 'mytable' is also used as the name of a regular table
then this isn't quite good enough, but I think just avoiding such a name
collision is easier than extending the check to reject non-temp tables.)

			regards, tom lane



From m_vijaykumar@hotmail.com Mon Sep  29 05:17:22 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id E5F60D1B50E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 29 Sep 2003 05:17:22 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 99383-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 29 Sep 2003 02:16:52 -0300 (ADT)
Received: from hotmail.com (bay2-f50.bay2.hotmail.com [65.54.247.50])
	by svr1.postgresql.org (Postfix) with ESMTP id CFBE4D1B546
	for <pgsql-sql@postgresql.org>; Mon, 29 Sep 2003 02:16:49 -0300 (ADT)
Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC;
	Sun, 28 Sep 2003 22:16:49 -0700
Received: from 202.63.107.19 by by2fd.bay2.hotmail.msn.com with HTTP;
	Mon, 29 Sep 2003 05:16:49 GMT
X-Originating-IP: [202.63.107.19]
X-Originating-Email: [m_vijaykumar@hotmail.com]
From: "vijaykumar M" <m_vijaykumar@hotmail.com>
To: jinujosein@yahoo.com, pgsql-sql@postgresql.org
Subject: Re: Temporary tables
Date: Mon, 29 Sep 2003 05:16:49 +0000
Mime-Version: 1.0
Content-Type: text/plain; format=flowed
Message-ID: <BAY2-F50EiLFkSOMF5Y0002892d@hotmail.com>
X-OriginalArrivalTime: 29 Sep 2003 05:16:49.0752 (UTC)
	FILETIME=[E2CF7580:01C38648]
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200309/373
X-Sequence-Number: 15351

Hi,

Try the below steps ..

1. Write one procedure to create tempory table (generic purpose)
**********************************************************************
CREATE OR REPLACE FUNCTION SP_CREATE_TEMP_TABLE () RETURNS VARCHAR AS '
DECLARE
L_SchemaName name;
BEGIN
	EXECUTE ''CREATE TEMPORARY TABLE temp_table_gen (X VARCHAR);'';
	SELECT schemaname INTO L_SchemaName FROM pg_stat_user_tables where relname 
=''temp_table_gen'';
	RETURN L_SchemaName;
END;
' LANGUAGE 'plpgsql';
**********************************************************************

2. Call the above (generic) procedure to get the temporary table schema 
name.. by using that schema name ..you can check whether the (real) 
temporary table is exists or not.

**************************************************************************
select into L_SchemaName * from SP_CREATE_TEMP_TABLE();  -- get the 
schemaname
	execute ''drop table temp_table_gen;'';   -- drop the temptable
	select schemaname into L_Schema from pg_stat_user_tables where 
relname=''temp_total_count''  and schemaname =''''||L_SchemaName||'''';
	if (L_Schema is null) then
		EXECUTE ''CREATE TEMPORARY TABLE temp_total_count (TOTAL_COUNT 
NUMERIC);'';
	ELSE
		EXECUTE ''DELETE FROM temp_total_count;'';
	END IF;
**************************************************************************

I hope this will help u to solve these temporary table issues..

With Regards
Vijay


>From: "George A.J" <jinujosein@yahoo.com>
>To: pgsql-sql@postgresql.org
>Subject: [SQL] Temporary tables
>Date: Sat, 27 Sep 2003 06:31:39 -0700 (PDT)
>
>
>hi,
>
>I am using postgresql 7.3.2. Is there any function to determine
>whether a table exists in the database.Or is there any function
>that returns the current temp schema.
>I am using a pl/pgsql function that create and drop a temporary table.
>The procedure run correctly for the first time for each database 
>connection.
>If I run the same procedure second time in the same connection it produces 
>the error
>
>"ERROR:  pg_class_aclcheck: relation 219389 not found
>WARNING:  Error occurred while executing PL/pgSQL function testFun
>WARNING:  line 20 at SQL statement "
>
>Here is the function ....
>
>---------------------------------------------------------
>CREATE OR REPLACE FUNCTION testFun( varchar(10) ) RETURNS setof int
>AS
>'
>DECLARE
>      --Aliases for parameters
>      vSBAcNo ALIAS FOR $1;
>      --local variables
>      vRow RECORD;
>
>BEGIN
>      -- create a tempory table to hold the numbers
>      CREATE TABLE tempTable
>      (
>           testNo int
>      ) ;
>
>     for vRow IN select Entryno from  EntryTable LOOP
>
>  return next vRow.Entryno;
>
>         insert into tempTable values( vRow.Entryno);
>
>     end loop;
>
>     drop table tempTable;
>
>     return;
>
>END;'
>
>LANGUAGE 'plpgsql';
>
>-------------------------------------------------------------
>
>If i commented the "insert into tempTable values( vRow.Entryno);" line
>the function works correctly. The problem is the oid of tempTable is kept 
>when
>the function is first executed. the next execution creates another table 
>with
>different oid. So the insert fails.
>
>I want to check whether the temporary table exist. If exist do not create 
>the
>temporary table in subsequent calls and do not dorp it. This will solve the 
>problem.
>
>When i searched the pg_class i found the temp table name more than once.
>ie, a temporary table is created for each connection.I cannot distingush
>the temp tables. But the tables are in different schema.
>Is there a method to get the current temporary schema? How postgres 
>distinguish
>this temp tables?.Is there a way to distinguish temporary tables.
>The entries in pg_class table is same except the schema.
>When i used the current_schema() function it returns public.
>
>There is a lot of functions that uses temporary tables. I think that there 
>is
>an option when creating temp tables in postgres 7.4 . But no way to use 7.4
>now it is a working database.
>
>can i write a function to check the existance of the temporary table...
>please help...
>
>jinujose
>
>
>---------------------------------
>Do you Yahoo!?
>The New Yahoo! Shopping - with improved product search

_________________________________________________________________
Keep up with the pace of change. Register for My Tech Ed. 
http://server1.msn.co.in/sp03/teched/index.asp Realise your potential!




From jinujosein@yahoo.com Wed Oct  1 04:06:30 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 7DC66D1DCF7
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  1 Oct 2003 04:06:30 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 32726-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  1 Oct 2003 01:05:40 -0300 (ADT)
Received: from web14901.mail.yahoo.com (web14901.mail.yahoo.com
	[216.136.225.53])
	by svr1.postgresql.org (Postfix) with SMTP id 7E428D1D597
	for <pgsql-sql@postgresql.org>; Wed,  1 Oct 2003 00:17:41 -0300 (ADT)
Message-ID: <20031001031740.66479.qmail@web14901.mail.yahoo.com>
Received: from [202.9.168.127] by web14901.mail.yahoo.com via HTTP;
	Tue, 30 Sep 2003 20:17:40 PDT
Date: Tue, 30 Sep 2003 20:17:40 -0700 (PDT)
From: "George A.J" <jinujosein@yahoo.com>
Subject: Re: Temporary tables
To: pgsql-sql@postgresql.org
In-Reply-To: <BAY2-F50EiLFkSOMF5Y0002892d@hotmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1620524479-1064978260=:66186"
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/2
X-Sequence-Number: 15402

--0-1620524479-1064978260=:66186
Content-Type: text/plain; charset=us-ascii


Thanks to all of you for your suggestions. the problem is solved by creating a function

istableexist() that returns whether a table exist or not. the function is bellow.


CREATE FUNCTION istableexist(varchar) RETURNS bool AS '

 DECLARE

 BEGIN

     /* check the table exist in database and is visible*/
     PERFORM relname,relnamespace FROM pg_class
            WHERE relkind = ''r''
                  AND Upper(relname) = Upper($1)
                  AND pg_table_is_visible(oid);

     IF FOUND THEN
        RETURN TRUE;
     ELSE
        RETURN FALSE;
     END IF;
   
 END;'
 LANGUAGE 'plpgsql';

thanks again

jinujose



---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
--0-1620524479-1064978260=:66186
Content-Type: text/html; charset=us-ascii

<DIV>
<P>Thanks to all of you for your suggestions. the problem is solved by creating a function</P>
<P>istableexist() that returns whether a table exist or not. the function is bellow.</P>
<P><BR>CREATE FUNCTION istableexist(varchar) RETURNS bool AS '</P>
<P>&nbsp;DECLARE</P>
<P>&nbsp;BEGIN</P>
<P>&nbsp; &nbsp; &nbsp;/* check the table exist in database and is visible*/<BR>&nbsp;&nbsp; &nbsp;&nbsp;PERFORM relname,relnamespace FROM pg_class<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;WHERE relkind = ''r''<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;AND Upper(relname) = Upper($1)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;AND pg_table_is_visible(oid);</P>
<P>&nbsp;&nbsp; &nbsp;&nbsp;IF FOUND THEN<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;RETURN TRUE;<BR>&nbsp;&nbsp; &nbsp;&nbsp;ELSE<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;RETURN FALSE;<BR>&nbsp;&nbsp; &nbsp;&nbsp;END IF;<BR>&nbsp;&nbsp; <BR>&nbsp;END;'<BR>&nbsp;LANGUAGE 'plpgsql';</P>
<P>thanks again</P>
<P>jinujose</P></DIV><p><hr SIZE=1>
Do you Yahoo!?<br>
<a href="http://shopping.yahoo.com/?__yltc=s%3A150000443%2Cd%3A22708228%2Cslk%3Atext%2Csec%3Amail">The New Yahoo! Shopping</a> - with improved product search
--0-1620524479-1064978260=:66186--