Rename a column if not already renamed.?

From: "Day, David" <david(dot)day(at)redcom(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Rename a column if not already renamed.?
Date: 2019-08-20 19:07:24
Message-ID: ef59a820008b4d3299428db52ecc32e0@redcom.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2019-08-20 19:16:24 Re: Rename a column if not already renamed.?
Previous Message pabloa98 2019-08-20 16:24:09 Re: SELECT all the rows where id is children of other node.