复制具有动态列名的记录 [英] Copy records with dynamic column names

问题描述

在PostgreSQL 9.3中，我有两个表具有不同的列:

I have two tables with different columns in PostgreSQL 9.3:

CREATE TABLE person1(
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL
);

CREATE TABLE person2(
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

INSERT INTO person2 (Name,    Age, ADDRESS, SALARY)
             VALUES ('Piotr', 20, 'London', 80);

我想将记录从person2复制到person1，但是列名可以在程序中更改，因此我想在程序中选择联合列名.因此，我创建了一个包含列名交集的数组.接下来，我使用一个函数:insert into .... select，但是当我按名称将数组变量传递给函数时，却出现了错误.像这样:

I would like to copy records from person2 to person1, but column names can change in program, so I would like to select joint column names in program. So I create an array containing the intersection of column names. Next I use a function: insert into .... select, but I get an error, when I pass the array variable to the function by name. Like this:

select column_name into name1 from information_schema.columns where table_name = 'person1';
select column_name into name2 from information_schema.columns where table_name = 'person2';
select * into cols from ( select * from name1 intersect select * from name2) as tmp;
-- Create array with name of columns 
select array (select column_name::text from cols) into cols2;

CREATE OR REPLACE FUNCTION f_insert_these_columns(VARIADIC _cols text[])
  RETURNS void AS
$func$
BEGIN
   EXECUTE (
      SELECT 'INSERT INTO person1 SELECT '
          || string_agg(quote_ident(col), ', ')
          || ' FROM person2'
      FROM   unnest(_cols) col
      );
END
$func$  LANGUAGE plpgsql;


select * from cols2;

  array    
------------
 {name,age}
(1 row)

SELECT f_insert_these_columns(VARIADIC cols2);

ERROR:  column "cols2" does not exist

这是怎么了?

推荐答案

答案

您似乎假设SQL中的SELECT INTO会分配一个变量.但是事实并非如此.

Answer

You seem to assume that SELECT INTO in SQL would assign a variable. But that is not so.

它会创建一个新的表，并且在Postgres中不鼓励使用它.请改用上级CREATE TABLE AS.尤其重要，因为plpgsql中SELECT INTO的含义是不同:

It creates a new table and its use is discouraged in Postgres. Use the superior CREATE TABLE AS instead. Not least, because the meaning of SELECT INTO inside plpgsql is different:

• 我之前曾建议过有关SQL变量的相关问题:

  I have suggested this related question about SQL variables before:

  • PostgreSQL中的用户定义变量

  因此，您不能这样调用该函数:

  Hence you cannot call the function like this:

  SELECT f_insert_these_columns(VARIADIC cols2);

  这将起作用:

  SELECT f_insert_these_columns(VARIADIC (TABLE cols2 LIMIT 1));
  

  关于简短的TABLE语法:

  • 是否存在SELECT的快捷方式*是来自psql吗?

  要复制两个表之间具有相同名称的列的所有行:

  To copy all rows with columns sharing the same name between two tables:

  CREATE OR REPLACE FUNCTION f_copy_rows_with_shared_cols(
      IN  _tbl1 regclass
    , IN  _tbl2 regclass
    , OUT rows int
    , OUT columns text) AS
  $func$
  BEGIN
     SELECT INTO columns                           -- proper use of SELECT INTO!
            string_agg(quote_ident(attname), ', ')
     FROM  (
        SELECT attname
        FROM   pg_attribute
        WHERE  attrelid IN (_tbl1, _tbl2)
        AND    NOT attisdropped  -- no dropped (dead) columns
        AND    attnum > 0        -- no system columns
        GROUP  BY 1
        HAVING count(*) = 2
        ) sub;
  
     EXECUTE format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %3$s'
                    , _tbl1, columns, _tbl2);
  
     GET DIAGNOSTICS rows = ROW_COUNT;  -- return number of rows copied
  END
  $func$  LANGUAGE plpgsql;
  

  致电:

  SELECT * FROM f_copy_rows_with_shared_cols('public.person2', 'public.person1');
  

  结果:

  rows | columns
  -----+---------
  3    | name, age
  

  要点

  • 请注意在plpgsql内部正确使用SELECT INTO进行分配.

    Major points

    • Note the proper use of SELECT INTO for assignment inside plpgsql.

      请注意使用数据类型regclass.这允许使用模式限定的表名(可选)，并防止SQL注入尝试:

      Note the use of the data type regclass. This allows to use schema-qualified table names (optionally) and defends against SQL injection attempts:

      • 表名作为PostgreSQL函数参数

      关于GET DIAGNOSTICS:

      • 计算受DELETE影响的行

      关于OUT参数:

      • 从具有OUT参数的函数返回

      有关format().

      查看全文