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