Thread: Reparse PL/pgSQL Function?

Is it possible to (automatically) force the reparse of a stored PL/pgSQL
function following the deletion and recreation of an object that was
referenced within the function (for example a table or another function)?

The need to re-create (create or replace ...) the function is a pain as
you have to follow a potential chain of other objects recreating them too.

I think that it would be better if Postgres could (optionally) try
re-parsing the function if it failed to locate a referenced object's OID.
The reparse would allow it to locate the newly created OID (assuming the
object had been recreated), and then operate as before.

I know that someone is going to say that it is safer not to do this
because the error flags the DB in an inconsistant state, but...

Oracle has the concept of a validity flag for stored procs/functions. When
you delete an object, all procs/functions referencing that object are
marked invalid. When you try to execute a function marked invalid, Oracle
reparses and compiles the proc/function the next time it is executed. If
the referenced objects have been recreated, then operation proceeds
normally, and if not, an error is generated.

Could Postgres provide the same behaviour? And if so, how likely is a fix? :)

John Sidney-Woollett

On Wed, 7 Jan 2004, John Sidney-Woollett wrote:

> Is it possible to (automatically) force the reparse of a stored PL/pgSQL
> function following the deletion and recreation of an object that was
> referenced within the function (for example a table or another function)?

Would

CREATE OR REPLACE function

work?

My take on the originmal question:
"If function Foo calls functioin Bar and you chanage Bar "sufficiently",
change the prameter list, return type.  etc.,  what would be necessary
to recompile Foo?"

Rick

scott.marlowe wrote:

>On Wed, 7 Jan 2004, John Sidney-Woollett wrote:
>
>
>>Is it possible to (automatically) force the reparse of a stored PL/pgSQL
>>function following the deletion and recreation of an object that was
>>referenced within the function (for example a table or another function)?
>>
>>
>
>Would
>
>CREATE OR REPLACE function
>
>work?
>

It would, but you have to locate all the functions that referenced your
deleted and recreated table... That's the pain.

If you miss one, you find out later when your app dies with an unexpected
error thrown by postgres.

John

scott.marlowe said:
> On Wed, 7 Jan 2004, John Sidney-Woollett wrote:
>
>> Is it possible to (automatically) force the reparse of a stored PL/pgSQL
>> function following the deletion and recreation of an object that was
>> referenced within the function (for example a table or another
>> function)?
>
> Would
>
> CREATE OR REPLACE function
>
> work?
>
>
>
>