Hello Adrian,
test=# UPDATE pg_attribute SET atttypmod = -1 WHERE attrelid =
'base_tbl'::regclass AND attname = 'vc_fld';
test=# UPDATE pg_attribute SET atttypmod = -1 WHERE attrelid =
'v_test'::regclass AND attname = 'vc_fld';
*This is exactly what I plan to do*. So, according to the test result,
can make conclusion that pg_attribute will auto take care of all
dependent views.
No you can not make that conclusion. I had to manually change the
atttypmod in the view.
You are right.
Well you are using a backdoor hack to directly alter a system table,
so yes there is a potential for problems.
I would imagine in this case, same base type just changing the length
argument
Confirm yes. only varchar(n) to varchar.
the chances of problems are slight.
So, how about the following steps:
begin;
set pg_attribute for v1; v2,... vN;
set pg_attribute for table;
commit;
What might be the left potential problems?
If not, I will adopt this approach since we have many view dependencies
and it seems that this was the best way to avoid view drop/re-creation
for now. If there are other ways, please do let me know.
The only other way I know to do this is to:
BEGIN;
DROP VIEW some_view ;
ALTER TABLE some_table ALTER COLUMN some_col TYPE new_type;
CREATE OR REPLACE VIEW some_view SELECT * FROM some_table;
COMMIT;
Comparing with the pg_attribute action, this approach would be the last
one since there are too many view dependencies.
Thanks a lot!
Emi
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
