Re: Rename a column if not already renamed.?

On 8/20/19 12:07 PM, Day, David wrote:
> I have a function used by my patch files which attempts to determine if the old column name exists, if so it
proceedswith the rename.
 
> 
> 
> CREATE OR REPLACE FUNCTION sys.rename_column(schema_table_ regclass, old_name_ text, new_name text)
>   RETURNS boolean
>   LANGUAGE plpgsql
> AS $function$
> BEGIN
> 
> IF EXISTS(SELECT 1 FROM pg_attribute
>            WHERE attrelid = schema_table_
>            AND attname = old_name_
>            AND NOT attisdropped) THEN
>      EXECUTE format ('ALTER TABLE %s RENAME %s TO %s', schema_table_, old_name_, new_name);
>      RETURN TRUE;
> ELSE
>     RETURN FALSE;
> END IF;
> 
> END;
> $function$;
> 
> 
> This seems to function correctly except.
> 
> If I am upgrading a site from Postgres 9.6 to version 11.3 and the column has already been renamed in the 9.6 branch
theabove existence test fails
 
> and an exception is generated.

Not clear to me when in process the function is run?

What is the exception generated?

> 
> It all seems to work correctly if I repeat this same patch in the 11.3 branch.
> 
> The function definition is the same for both branches.
> 
> I suspect I am overlooking some fundamental issue here.
> Anyone with a thought.
> 
> 
> Thanks
> 
> 
> Dave Day
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com