pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem with temporary table.
2+ messages / 2 participants
[nested] [flat]
* problem with temporary table.
@ 2003-07-10 10:03 Vijay Kumar <vijay@kinera.com>
2003-07-16 13:42 ` Re: problem with temporary table. Jonathan Gardner <jgardner@jonathangardner.net>
0 siblings, 1 reply; 2+ messages in thread
From: Vijay Kumar @ 2003-07-10 10:03 UTC (permalink / raw)
To: pgsql-sql
Hi,
We are using postgresql7.3.3, we are encountering some problems by using temporary tables.
Actually our requirement was,
1. create temporary table.
2. insert some values on that table by using some quries.
3. select the inserted values from the temporary table.
To fullfil the above requirement, we wrote the below functions..kindly go through the below functions and
guide us to come out from this temporary table problem.
1. Call the same function more than ones in the same connection.
eg;
create or replace function TestTemp_refcur(refcursor) returns refcursor as '
declare
refc alias for $1;
begin
create temporary table temp_table(idno numeric,iname varchar(10));
insert into temp_table values (1,''ganesh'');
insert into temp_table values (2,''John'');
open refc for select * from test_temp_table;
return refc;
end;
' language 'plpgsql';
begin;
select TestTemp_refcur('funcursor');
fetch all in funcursor;
commit;
The above function is working fine for the first call, from next call onwards it is throwing the below error.
Error: relation 'temp_table' already exists.
2. To avoid this, we had gone through some of postgresql faq and documents. There some one suggested to create temporary table by Execute.
So that, we created one function, inside that fucntion we created one more function to take care of creating temporary table.
Eg,
create or replace function TestTemp_refcur(refcursor) returns refcursor As '
declare
refc alias for $1;
lString Varchar(4000);
begin
lString := '' CREATE OR REPLACE FUNCTION TESTTEMP() RETURNS varchar as ''''
BEGIN '';
lString := lString || '' create temporary table temp_Table( Name Numeric);'';
lString := lString || '' insert into temp_Table values (1); '';
lString := lString || '' insert into temp_Table values (2);'';
lString := lString || '' return null; end;'''' language ''''plpgsql'''';'';
raise notice '' Notice is % '', lString;
execute lString;
open refc for select * from temp_Table;
return refc;
end;
' language 'plpgsql';
begin;
select TestTemp_refcur('funcursor');
fetch all in funcursor;
commit;
With the above approach, we are getting the below error.
Error : Relation "temp_table" does not exist
Any kind of info/soln/help will be highly appreciated..
Thanks & Regards
Vijay
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: problem with temporary table.
2003-07-10 10:03 problem with temporary table. Vijay Kumar <vijay@kinera.com>
@ 2003-07-16 13:42 ` Jonathan Gardner <jgardner@jonathangardner.net>
0 siblings, 0 replies; 2+ messages in thread
From: Jonathan Gardner @ 2003-07-16 13:42 UTC (permalink / raw)
To: Vijay Kumar <vijay@kinera.com>; pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Thursday 10 July 2003 03:03, Vijay Kumar wrote:
> Hi,
> We are using postgresql7.3.3, we are encountering some problems by
> using temporary tables.
>
> Actually our requirement was,
> 1. create temporary table.
> 2. insert some values on that table by using some quries.
> 3. select the inserted values from the temporary table.
>
< snip>
>
> The above function is working fine for the first call, from next
> call onwards it is throwing the below error. Error: relation 'temp_table'
> already exists.
>
So drop the table when you are done with it.
You may also want to investigate returning a table of data, rather than a
cursor or a single row. I am not too sure on how this would work exactly
because I have never done it. Someone else may be willing to fill in the
details.
- --
Jonathan Gardner <jgardner@jonathangardner.net>
(was jgardn@alumni.washington.edu)
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/FVY4WgwF3QvpWNwRAjIXAJ47L28D29zv91JGXQnA1rQ79wqRlwCeLB56
/+FhmG0tosyNXyH61po2myY=
=UWtn
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-07-16 13:42 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-10 10:03 problem with temporary table. Vijay Kumar <vijay@kinera.com>
2003-07-16 13:42 ` Jonathan Gardner <jgardner@jonathangardner.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox