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 proceeds with 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 the above 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(dot)klaver(at)aklaver(dot)com