Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 86E9E9F9F7F
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 17 Nov 2006 07:02:19 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 06284-09 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 17 Nov 2006 07:02:17 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by postgresql.org (Postfix) with ESMTP id 84B0F9F9F7E
	for <pgsql-sql@postgresql.org>; Fri, 17 Nov 2006 07:02:16 -0400 (AST)
Received: from mainbox.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id E34A7762B9;
	Fri, 17 Nov 2006 11:02:10 +0000 (GMT)
Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP id AE95F15EAB;
	Fri, 17 Nov 2006 11:02:10 +0000 (GMT)
Message-ID: <455D9755.7090206@archonet.com>
Date: Fri, 17 Nov 2006 11:04:53 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5.0.7 (X11/20060918)
MIME-Version: 1.0
To: "Penchalaiah P." <penchalaiahp@infics.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: hi may i know y am i getting this error
References: <A70AE049E81A6A40879D5DC0AC8C38C9078D7304@venus.infics.com>
In-Reply-To: <A70AE049E81A6A40879D5DC0AC8C38C9078D7304@venus.infics.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200611/182
X-Sequence-Number: 26834

Penchalaiah P. wrote:
> Hi
> 
> When I am executing my function its giving error.. first time its
> running properly.. but when I am executing second time I am getting
> error

> create table dummy_table without oids as
...
> update dummy_table set irla_code=l_p where irla_code=''P'';
...
>             ERROR:  relation with OID 75275 does not exist
> 
> CONTEXT:  SQL statement "update dummy_table set irla_code= $1  where
> irla_code='P'"

The queries in plpgsql have their query-plans compiled the first time 
they are executed. This means that the first time you ran the function 
you created dummy_table with an OID of 75275. The second time, it was 
re-created with a different OID, but the update query doesn't know this. 
Its plan tries to access a table with OID=75275 and fails.

Solution: read up on the EXECUTE <string> statement that allows you to 
build dynamic queries.

Also, you might want to read up on creating temporary tables.

HTH
-- 
   Richard Huxton
   Archonet Ltd