Обсуждение: calling function
Hi All,
I created a following function which works fine when I use this query in
winsql client to execute it
SELECT * FROM getAccomms('en_GB') AS accomm(id INTEGER, name VARCHAR,
min_child_age SMALLINT, max_child_age SMALLINT, image_id VARCHAR, url
VARCHAR, alt_text VARCHAR);
But using java code it doesn't work fine. It just hangs. After this I close
my java program and again execute the above query using winsql it gives
error
Error: ERROR: relation with OID 659490 does not exist (State:S1000, Native
Code: 7)
Please tell me what is the problem?
CREATE OR REPLACE FUNCTION getAccomms(VARCHAR) RETURNS SETOF record AS '
DECLARE
locale ALIAS FOR $1;
accomms RECORD;
images RECORD;
innerDelimiter VARCHAR := ''#'';
id_var VARCHAR := '''';
url_var VARCHAR := '''';
alt_text_var VARCHAR := '''';
BEGIN
CREATE TABLE temp_accomm (
id INTEGER,
name VARCHAR(100),
min_child_age SMALLINT,
max_child_age SMALLINT,
image_id VARCHAR(100),
url VARCHAR(500),
alt_text VARCHAR(500)
);
FOR accomms IN
SELECT a.id, atxt.name, a.min_child_age, a.max_child_age
FROM accomm AS a
INNER JOIN accomm_text AS atxt ON atxt.accomm_id = a.id
WHERE atxt.locale = locale
LOOP
INSERT INTO temp_accomm
(id, name, min_child_age, max_child_age)
VALUES (accomms.id, accomms.name, accomms.min_child_age,
accomms.max_child_age);
FOR images IN
SELECT image.id, image.url, image.alt_text
FROM image
INNER JOIN accomm_images ON accomm_images.image_id = image.id
WHERE accomm_images.accomm_id = accomms.id
LOOP
id_var := id_var || images.id || innerDelimiter;
url_var := url_var || images.url || innerDelimiter;
alt_text_var := alt_text_var || images.alt_text ||
innerDelimiter;
END LOOP;
id_var := trim(trailing innerDelimiter from id_var);
url_var := trim(trailing innerDelimiter from url_var);
alt_text_var := trim(trailing innerDelimiter from alt_text_var);
UPDATE temp_accomm
SET image_id = id_var, url = url_var, alt_text = alt_text_var
WHERE id = accomms.id;
id_var := '''';
url_var := '''';
alt_text_var := '''';
END LOOP;
FOR accomms IN
SELECT id, name, min_child_age, max_child_age, image_id, url,
alt_text
FROM temp_accomm
LOOP
RETURN NEXT accomms;
END LOOP;
DROP TABLE temp_accomm;
RETURN;
END;
' LANGUAGE 'plpgsql';
________________________________________________________________________
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________
Bhushan Bhangale <bbhangale@Lastminute.com> writes:
> Error: ERROR: relation with OID 659490 does not exist (State:S1000, Native
> Code: 7)
That function is only going to work once per session, because plpgsql
caches query plans. After the first call, the commands in the loop will
refer to a version of temp_accomm that doesn't exist anymore.
AFAICS you do not actually need the temp_accum table anyway. Why don't
you just return the data directly from the main loop?
regards, tom lane
Hi Tom,
Well actually it worked via java now.
But I am really interested in what you said about first call.
I am creating the temp_accomm table everytime I call the function and drop
it in the end.
Let me explain the functionality. I need to get the result of a query which
joins 4 tables. The problem is because of this join I get more than one
record for one accomodation which is fine as there are multiple child images
for a accomm. What I have to do is concatenate the column of image table and
return in the resultset.
The out put looks like this
412|Hotel Cristallo|0|0|901#902|r82h06.jpg#r82h06.jpg|Hotel Cristallo#Hotel
Cristallo
413|Campo Smith Village|0|0|903#904|r83a06.jpg#r83a06.jpg|Campo Smith
Village and Great Western Hotel Rive#Campo Smith Village and Great Western
Hotel Rive
Do you still see any problem here.
One more thing in MS-SQL I used to create temp table using #. How can I do
that here in postgres?
Thanks
Bhushan
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 02 March 2004 15:16
To: Bhushan Bhangale
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] calling function
Bhushan Bhangale <bbhangale@Lastminute.com> writes:
> Error: ERROR: relation with OID 659490 does not exist (State:S1000,
Native
> Code: 7)
That function is only going to work once per session, because plpgsql
caches query plans. After the first call, the commands in the loop will
refer to a version of temp_accomm that doesn't exist anymore.
AFAICS you do not actually need the temp_accum table anyway. Why don't
you just return the data directly from the main loop?
regards, tom lane
________________________________________________________________________
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________
________________________________________________________________________
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________
Standard queries in plpgsql are parsed / planned only once per backend
session, and the plan stores the object ids of the relations used in
the query, not the names. So, when you drop the table, the cached plan
becomes invalid. If your client disconnects / reconnects, then calls
the function, it will be parsed / planned again, and all is well --
that one time.
If you want to execute queries without having plans cached, you need to
make use of the execute command, the technique specified in section
37.6.4 of the postgres manual:
http://www.postgresql.org/docs/7.4/static/plpgsql-statements.html
But, as Tom said, you can probably rephrase the function to not use a
temp table anymore.
----
James Robinson
Socialserve.com
Well I would like to use function with plan cached. But how come the table
which I create in the function is part of that. When ever I call the
function it create a table and then drops it. So should not be a problem and
even its not coming.
Initially I tried to write the function without using a temp table but
couldn't.
thanks
Bhushan
-----Original Message-----
From: James Robinson [mailto:jlrobins@socialserve.com]
Sent: 03 March 2004 14:55
To: pgsql-jdbc@postgresql.org
Cc: bbhangale@Lastminute.com
Subject: Re: calling function
Standard queries in plpgsql are parsed / planned only once per backend
session, and the plan stores the object ids of the relations used in
the query, not the names. So, when you drop the table, the cached plan
becomes invalid. If your client disconnects / reconnects, then calls
the function, it will be parsed / planned again, and all is well --
that one time.
If you want to execute queries without having plans cached, you need to
make use of the execute command, the technique specified in section
37.6.4 of the postgres manual:
http://www.postgresql.org/docs/7.4/static/plpgsql-statements.html
But, as Tom said, you can probably rephrase the function to not use a
temp table anymore.
----
James Robinson
Socialserve.com
________________________________________________________________________
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________
________________________________________________________________________
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________
Here's a before and after version of a simplified version of what
you're trying to do in your script. The 'after' version uses 'execute'
and 'for row in exectue ... loop' to not cache plans. Also notice the
use of a temporary table, visible only to this backend, in case more
than one backend calls the function simultaneously.
Any other discussion should be done off of this list, as this is not
JDBC related at all.
James
-- simplified version of inital plpgsql function. Gets bitten by cached
query plan.
create or replace function f() returns setof record as '
DECLARE
row RECORD;
BEGIN
create table foo
(
i int
);
insert into foo values(1);
insert into foo values(2);
FOR row in select * from foo LOOP
RETURN NEXT row;
END LOOP;
drop table foo;
return;
END;
' LANGUAGE 'plpgsql';
-- works
select * from f() as f_results(id int);
-- fails on any subsequent call in this session
select * from f() as f_results(id int);
-- now a version that will work more than once per session
-- note the use of execute and for ... in execute
create or replace function f() returns setof record as '
DECLARE
row RECORD;
BEGIN
create temporary table foo
(
i int
);
execute ''insert into foo values(1)'';
execute ''insert into foo values(2)'';
FOR row in execute ''select * from foo'' LOOP
RETURN NEXT row;
END LOOP;
drop table foo;
return;
END;
' LANGUAGE 'plpgsql';
-- now can call many times per session
select * from f() as f_results(id int);
select * from f() as f_results(id int);
select * from f() as f_results(id int);
select * from f() as f_results(id int);
----
James Robinson
Socialserve.com
I have :
Connection is working. I can insert but not select...
Have anyone such experience?
ORA-00600: internal error code, arguments: [ttcgcshnd-1], [0], [], [], [],
[], [], []
This is visible exactly when I use rs.next() with ResultSet
What could it be? HELP
Written by Mariusz Wyrozebski
moon@mat.uni.torun.pl
tel: 692 654 059
Mariusz Wyrozebski said: > I have : > Connection is working. I can insert but not select... > > Have anyone such experience? > > ORA-00600: internal error code, arguments: [ttcgcshnd-1], [0], [], [], [], > [], [], [] > > This is visible exactly when I use rs.next() with ResultSet Why are you asking for help with an Oracle error code on the Postgres list? :) Are you sure you're using the correct connection/datasource/driver? John Sidney-Woollett
Mariusz, With the information you have provided it could be anything, how would you expect us to be able to help you? we need server version, jdbc version, select statement etc.... Dave On Thu, 2004-03-04 at 09:23, Mariusz Wyrozebski wrote: > I have : > Connection is working. I can insert but not select... > > Have anyone such experience? > > ORA-00600: internal error code, arguments: [ttcgcshnd-1], [0], [], [], [], > [], [], [] > > This is visible exactly when I use rs.next() with ResultSet > > What could it be? HELP > > Written by Mariusz Wyrozebski > moon@mat.uni.torun.pl > tel: 692 654 059 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Dave Cramer 519 939 0336 ICQ # 14675561
i've forgotten : connection is working! driver is good but what Yuo mean
datasource?
Written by Mariusz Wyrozebski
moon@mat.uni.torun.pl
tel: 692 654 059
On Thu, 4 Mar 2004, John Sidney-Woollett wrote:
> Mariusz Wyrozebski said:
> > I have :
> > Connection is working. I can insert but not select...
> >
> > Have anyone such experience?
> >
> > ORA-00600: internal error code, arguments: [ttcgcshnd-1], [0], [], [], [],
> > [], [], []
> >
> > This is visible exactly when I use rs.next() with ResultSet
>
> Why are you asking for help with an Oracle error code on the Postgres
> list? :)
>
> Are you sure you're using the correct connection/datasource/driver?
>
> John Sidney-Woollett
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
The error message indicates you are using an Oracle database. This mailing list is for the PostgreSQL database and associated jdbc driver. You should contact Oracle for support of their product. --Barry Mariusz Wyrozebski wrote: > I have : > Connection is working. I can insert but not select... > > Have anyone such experience? > > ORA-00600: internal error code, arguments: [ttcgcshnd-1], [0], [], [], [], > [], [], [] > > This is visible exactly when I use rs.next() with ResultSet > > What could it be? HELP > > Written by Mariusz Wyrozebski > moon@mat.uni.torun.pl > tel: 692 654 059 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)