在Postgresql中将列的注释设置为另一列的注释 [英] Setting the comment of a column to that of another column in Postgresql

问题描述

假设我在Postgresql中创建了一个对列有注释的表：

 创建表t1（
 c1 varchar（10）
）; 
对t1.c1列的注释是'foo';一段时间后，我决定添加另一列：
 
 $ b 
 
 
  alter table t1 add column c2 varchar（20）; 
  
我想查找第一列的注释内容，并与新列相关联： / p> 
 
 
  select（what？）where table_name ='t1'and column_name ='c1'
  
（什么？）将是一个系统表，但是在pgAdmin中查找并在Web上搜索后，我没有学会了它的名字。
 
 
 
理想情况下，我希望能够：
 对列t1.c1的注释是（select ...）; 
  
但我有一种感觉，伸展的东西有点远。非常感谢任何想法。
 
 
 
更新：根据我在这里收到的建议，我结束了编写一个程序，以自动化转移评论的任务，作为一个更大的过程的一部分更改Postgresql列的数据类型。您可以在我的博客中阅读。 
解决方案
接下来要知道的是如何获取表oid。 
 
 postgres =＃create table comtest1（id int， val varchar）; 
 CREATE TABLE 
 postgres =＃insert into comtest1 values（1，'a'）; 
 INSERT 0 1 
 postgres =＃select distinct tableoid from comtest1; 
 tableoid 
 ---------- 
 32792 
（1 row）
 
 postgres =＃comtest1.id列上的注释'标识符号码'; 
 COMMENT 
 postgres =＃select col_description（32792,1）; 
 col_description 
 ----------------------- 
标识符编号一
（1行）
 
 
 
 
无论如何，我打开了一个快速的plpgsql函数，将注释从一个表/列对复制到另一个。您必须在数据库上创建plpgsql并使用它：
 
将表comtest1的第一列上的注释复制到id 
表comtest2的列。是的，应该改进，但
留给读者工作。 
 
 postgres =＃select copy_comment（'comtest1'，1，'comtest2'，'id'）; 
 copy_comment 
 -------------- 
 1 
（1 row）
 
 
 
 
  CREATE OR REPLACE FUNCTION copy_comment（varchar，int，varchar，varchar）RETURNS int AS $ PROC $ 
 DECLARE 
 src_tbl ALIAS $ 1 ; 
 src_col ALIAS $ 2; 
 dst_tbl ALIAS $ 3; 
 dst_col ALIAS $ 4; 
 row RECORD; 
 oid INT; 
 comment VARCHAR; 
 BEGIN 
 FOR row IN EXECUTE'SELECT DISTINCT tableoid FROM'|| quote_ident（src_tbl）LOOP 
 oid：= row.tableoid; 
 END LOOP; 
 
 FOR row IN EXECUTE'SELECT col_description（'|| quote_literal（oid）||'，'|| quote_literal（src_col）||'）'LOOP 
 comment：= row.col_description ; 
 END LOOP; 
 
执行语句'COMMENT ON COLUMN'|| quote_ident（dst_tbl）|| '。'|| quote_ident（dst_col）|| 'IS'|| quote_literal（comment）; 
 
返回1; 
 END; 
 $ PROC $ LANGUAGE plpgsql; 
  
 
Suppose I create a table in Postgresql with a comment on a column:
create table t1 (
   c1 varchar(10)
);
comment on column t1.c1 is 'foo';
Some time later, I decide to add another column:
alter table t1 add column c2 varchar(20);
I want to look up the comment contents of the first column, and associate with the new column:
select comment_text from (what?) where table_name = 't1' and column_name = 'c1'
The (what?) is going to be a system table, but after having looked around in pgAdmin and searching on the web I haven't learnt its name.

Ideally I'd like to be able to:
comment on column t1.c1 is (select ...);
but I have a feeling that's stretching things a bit far. Thanks for any ideas.

Update: based on the suggestions I received here, I wound up writing a program to automate the task of transferring comments, as part of a larger process of changing the datatype of a Postgresql column. You can read about that on my blog.
解决方案
The next thing to know is how to obtain the table oid. I think that using this as part of comment on will not work, as you suspect.
    postgres=# create table comtest1 (id int, val varchar);
    CREATE TABLE
    postgres=# insert into comtest1 values (1,'a');
    INSERT 0 1
    postgres=# select distinct tableoid from comtest1;
     tableoid
    ----------
        32792
    (1 row)

    postgres=# comment on column comtest1.id is 'Identifier Number One';
    COMMENT
    postgres=# select col_description(32792,1);
        col_description
    -----------------------
     Identifier Number One
    (1 row)
Anyhow, I whipped up a quick plpgsql function to copy comments from one table/column pair to another. You have to createlang plpgsql on the database and use it like this:
    Copy the comment on the first column of table comtest1 to the id 
    column of the table comtest2. Yes, it should be improved but 
    that's left as work for the reader.

    postgres=# select copy_comment('comtest1',1,'comtest2','id');
     copy_comment
    --------------
                1
    (1 row)


CREATE OR REPLACE FUNCTION copy_comment(varchar,int,varchar,varchar) RETURNS int AS $PROC$
DECLARE
        src_tbl ALIAS FOR $1;
        src_col ALIAS FOR $2;
        dst_tbl ALIAS FOR $3;
        dst_col ALIAS FOR $4;
        row RECORD;
        oid INT;
        comment VARCHAR;
BEGIN
        FOR row IN EXECUTE 'SELECT DISTINCT tableoid FROM ' || quote_ident(src_tbl) LOOP
                oid := row.tableoid;
        END LOOP;

        FOR row IN EXECUTE 'SELECT col_description(' || quote_literal(oid) || ',' || quote_literal(src_col) || ')' LOOP
                comment := row.col_description;
        END LOOP;

        EXECUTE 'COMMENT ON COLUMN ' || quote_ident(dst_tbl) || '.' || quote_ident(dst_col) || ' IS ' || quote_literal(comment);

        RETURN 1;
END;
$PROC$ LANGUAGE plpgsql;


                        
这篇关于在Postgresql中将列的注释设置为另一列的注释的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！