And that is a different issue. There is some sort of internal optimization
in PG/pgSQL where objects are dereferenced and their OIDs are stored
instead. This is sort of a general problem: if you delete something if
other things refer to it then you may have to recreate them as well. The
exception here is in plain SQL functions and in EXECUTE blocks in
PL/pgSQL. In both of those cases the SQL code is parsed and executed at
runtime. In other cases (I'm thinking of functions, views, triggers) the
reference is symbolic 'pepe'::text until the symbolic reference is
dereferenced and then it is converted to a hard reference like 16651::oid.
All that means is that you drop and create your x(..,..) function after
drop/create on your pepe table. If you do all the work inside of a
transaction I suppose you can do all the modifications transparently to
other users. This works exactly like if you had a view or other tables
that use the pepe view. *same* issue.
I seem to recall folks on [hackers] mentioning something about making
working with dependant objects easier so this might change eventually.
Joshua b. Jore ; http://www.greentechnologist.org
On Sat, 6 Jul 2002, Terry Yapt wrote:
> But, I wants to test inserting more rows and then I have re-created (drop and create) table 'pepe' varying column 'a'
fromnumeric(2) to numeric(8), in this way:
> =============
> Table "pepe"
> Column | Type | Modifiers
> --------+-----------------------+-----------
> a | numeric(8,0) | not null
> b | character varying(50) |
> Primary key: pepe_pkey
> =============
>
> Then If I call the function from 'psql' it give me this error (but with pgadmin 2 the same 'select x(10,40) runs
fine):
>
> NOTICE: Error occurred while executing PL/pgSQL function x
> NOTICE: line 9 at SQL statement
> ERROR: Relation 16651 does not exist
>
> Thanks....
>