473,899 Members | 3,071 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

functions and temporary tables

hi,

i'm writing some plpgsql functions which use a temporary table, and i've
read the FAQ and am using EXECUTE to create and insert into my table to
avoid errors caused by postgres caching the query plan. however, i can't
work out how to get the data back out of my temporary table, as i don't
think i can get the results of a select performed using EXECUTE? if i just
do the select directly, once the temporary table has been recreated, the
select fails with the error "relation with OID xxxxx does not exist". Can
anyone suggest how I can void this and get data back out of my temp table?

I've pasted the functions at the end of the mail if it helps.
Thanks,
Tamsin

CREATE OR REPLACE FUNCTION setAppUser (TEXT) RETURNS BOOLEAN AS '
DECLARE
uname alias for $1;

BEGIN
IF isTable(''app_u ser'') THEN
EXECUTE ''DELETE FROM app_user'';
ELSE
EXECUTE ''CREATE TEMPORARY TABLE app_user (username VARCHAR(50)) ON
COMMIT DROP'';
END IF;
EXECUTE ''INSERT INTO app_user VALUES (''''''||uname| |'''''')'';

RETURN TRUE;
END;
' LANGUAGE 'plpgsql';

--RETURNS THE APP USERNAME IF THERE IS ONE
CREATE OR REPLACE FUNCTION getAppUser () RETURNS VARCHAR AS '
DECLARE
user_record RECORD;

BEGIN
IF isTable(''app_u ser'') THEN
SELECT INTO user_record * FROM app_user;
IF NOT FOUND THEN
RETURN '''';
ELSE
RETURN user_record.use rname;
END IF;
ELSE
RETURN '''';
END IF;
END;
' LANGUAGE 'plpgsql';


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #1
4 15830
Hi,
when I solved a very similar problem and I finally left idea about
temporary tables and I used something as following:
- create some permanent table(s) to store connection-specific
informations with added column 'pid' (which is primary key).
- when you insert some row into this table, use pg_backend_pid( ) as
primary key
- when you select propper row, use clause 'where pid = pg_backend_pid( )'
- be carefull about "dead" rows (it's pid does not correspond with
existing pg backend yet)

regards,
pajout

anorakgirl wrote:
hi,

i'm writing some plpgsql functions which use a temporary table, and i've
read the FAQ and am using EXECUTE to create and insert into my table to
avoid errors caused by postgres caching the query plan. however, i can't
work out how to get the data back out of my temporary table, as i don't
think i can get the results of a select performed using EXECUTE? if i just
do the select directly, once the temporary table has been recreated, the
select fails with the error "relation with OID xxxxx does not exist". Can
anyone suggest how I can void this and get data back out of my temp table?

I've pasted the functions at the end of the mail if it helps.
Thanks,
Tamsin

CREATE OR REPLACE FUNCTION setAppUser (TEXT) RETURNS BOOLEAN AS '
DECLARE
uname alias for $1;

BEGIN
IF isTable(''app_u ser'') THEN
EXECUTE ''DELETE FROM app_user'';
ELSE
EXECUTE ''CREATE TEMPORARY TABLE app_user (username VARCHAR(50)) ON
COMMIT DROP'';
END IF;
EXECUTE ''INSERT INTO app_user VALUES (''''''||uname| |'''''')'';

RETURN TRUE;
END;
' LANGUAGE 'plpgsql';

--RETURNS THE APP USERNAME IF THERE IS ONE
CREATE OR REPLACE FUNCTION getAppUser () RETURNS VARCHAR AS '
DECLARE
user_record RECORD;

BEGIN
IF isTable(''app_u ser'') THEN
SELECT INTO user_record * FROM app_user;
IF NOT FOUND THEN
RETURN '''';
ELSE
RETURN user_record.use rname;
END IF;
ELSE
RETURN '''';
END IF;
END;
' LANGUAGE 'plpgsql';


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #2
On Mon, 9 Feb 2004, anorakgirl wrote:
i'm writing some plpgsql functions which use a temporary table, and i've
read the FAQ and am using EXECUTE to create and insert into my table to
avoid errors caused by postgres caching the query plan. however, i can't
work out how to get the data back out of my temporary table, as i don't
think i can get the results of a select performed using EXECUTE? if i just


IIRC, to get data out from an execute at this point requires using the FOR
record IN EXECUTE querystring LOOP ... END LOOP; construct.

Something like:

CREATE OR REPLACE FUNCTION getAppUser () RETURNS VARCHAR AS '
DECLARE
user_record RECORD;

BEGIN
IF isTable(''app_u ser'') THEN
FOR user_record IN EXECUTE ''SELECT * FROM app_user'' LOOP
RETURN user_record.use rname;
END LOOP;
RETURN '''';
ELSE
RETURN '''';
END IF;
END;
' LANGUAGE 'plpgsql';

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #3
brilliant it works thank you!

(thanks for the alternate suggestion too pajout, i was just resigning
myself to rewriting everything using your method)

cheers,
tamsin
On Mon, 9 Feb 2004, anorakgirl wrote:
i'm writing some plpgsql functions which use a temporary table, and
i've read the FAQ and am using EXECUTE to create and insert into my
table to avoid errors caused by postgres caching the query plan.
however, i can't work out how to get the data back out of my temporary
table, as i don't think i can get the results of a select performed
using EXECUTE? if i just


IIRC, to get data out from an execute at this point requires using the
FOR record IN EXECUTE querystring LOOP ... END LOOP; construct.

Something like:

CREATE OR REPLACE FUNCTION getAppUser () RETURNS VARCHAR AS '
DECLARE
user_record RECORD;

BEGIN
IF isTable(''app_u ser'') THEN
FOR user_record IN EXECUTE ''SELECT * FROM app_user'' LOOP
RETURN user_record.use rname;
END LOOP;
RETURN '''';
ELSE
RETURN '''';
END IF;
END;
' LANGUAGE 'plpgsql';



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #4
"anorakgirl " <po******@anora kgirl.co.uk> writes:
... i can't
work out how to get the data back out of my temporary table, as i don't
think i can get the results of a select performed using EXECUTE?


At present you have to use a FOR IN EXECUTE loop, even if you only
expect one row out of the SELECT.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2210
by: Peter Gorelczenko | last post by:
I'm running as normal user (not root or database owner). This user has c= reate=20 temporary table priv. show tables partial: GRANT CREATE TEMPORARY TABLES ON `foobar0`.* TO 'foo'@'localhost' -> (foo= bar0=20 and foo are masks for the database name and user, respectively) I created two tmp tables with intermdiate results. I then try to join th=
0
2096
by: Soefara | last post by:
Dear Sirs, I have been developing an application on Windows with MySQL 3.23, making use of temporary tables. Now when I try to port the application to a Unix box running also MySQL 3.23, I keep running into "access denied" errors with the queries which employ temporary tables. This only happens with CREATE TEMPORARY TABLE type queries, all other queries work fine.
2
4027
by: Ryan | last post by:
Just a quicky about temporarary tables. If using QA, when you create a temporary table, it gets dropped if you close the query. Otherwise you need to state 'DROP TABLE myTable' so that you can re-run the query without the table being there. Sometimes, you can have quite lengthy SQL statements (in a series) with various drop table sections throughout the query. Ideally you would put these all at the end, but sometimes you will need to...
2
6984
by: Keith Watson | last post by:
Hi, we are currently implementing an application running on DB2 V7 on Z/OS using largely COBOL stored procedures, managed using WLM. Some of these stored procedures declared global temporary tables, which are declared with ON COMMIT DELETE ROWS to perform work on and then return these temporary tables to the client (which is a message driven EJB connecting via DB2 connect). The client reads the data in the result sets, creates some XML...
2
2372
by: Chuck Crews | last post by:
I am interested in declaring a global temporary table within an application. The application processes 1 set of 600 or less rows each iteration. Multiple programs can and do call this one application at the same time. Today, we are using a permanent DB2 table and getting all kinds of locking even with row level locking on. In addition, our performance is slow as we do two inserts and two deletes on each row per program iteration. ...
3
3282
by: Mike Ridley | last post by:
I have 2 databases called (for example) "progs.mdb" and "files.mdb". Both these databases reside on computer "myserver". The progs database has links to the tables in the files database. "myclient1" runs a shortcut "msaccess.exe \\myserver\progs.mdb /x startmacro". "myclient2" runs his shortcut "msaccess.exe \\myserver\progs.mdb /x startmacro". If I use a temporary table I take it that it will be created on "myserver" in the...
0
1367
by: Zlatko Matić | last post by:
I tried to work with postgres temporary tables from MS Access, but unsuccessfully... I was able to create temporary table by pass-through query, also I succeeded in creating linked table through code, but when I try to open it, an error apears and Access says there is no schema... Also, I couldn't link table using ODBC wizard... Can someone explain why I can't use temporary tables from MS Access ? Does it mean that every query execution...
1
27055
by: Stefan van Roosmalen | last post by:
Hi there, Is there a way to list the TEMPORATY tables? I have tried SHOW TABLES, but this command only list the regular tables. Thank you very much for your answer. Regards, Stefan.
3
6884
by: comp_databases_ms-sqlserver | last post by:
This post is related to SQL server 2000 and SQL Server 2005 all editions. Many of my stored procedures create temporary tables in the code. I want to find a way to find the query plan for these procs Repro --*********************************** use pubs go CREATE PROCEDURE Test @percentage int
0
9997
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9843
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10971
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10494
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
8039
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7201
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5887
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6081
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4720
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.