postgresql - 表»rowtype«缺少FROM子句条目

我正在用plpgsql语言编写一个函数来创建分区，它将保存每个月的传感器数据（一个分区一个月和传感器）。我陷入了这个错误：

ERROR: missing FROM-clause entry for table »curr_sensor«
SQL state: 42P01
Context: SQL-Query »CREATE TABLE sensor_1_201609 (CHECK (timestamp >= date(curr_sensor.timestamp) AND timestamp < (date(curr_sensor.timestamp) + interval '1 month'))) INHERITS (sensor_fake_data)«
PL/pgSQL-Function create_partition_per_sensor_and_month() Line 20 at EXECUTE

函数如下所示：

CREATE OR REPLACE FUNCTION create_partition_per_sensor_and_month() RETURNS text as
$BODY$
-- declare variables
DECLARE
    loop_count integer := 0;
    curr_sensor sensor_fake_data%rowtype;
    curr_partition_limit timestamp;
    partition_table_name text;
    partition_index_timestamp_name text;
    partition_index_id_name text;
-- begin with the for loop...loop over every sensor and partition based on sensor and the timestamp
BEGIN                                                                   --replace mumber with variable
    FOR curr_sensor IN SELECT * FROM sensor_fake_data WHERE sensor_id = 1 ORDER BY timestamp ASC
    LOOP
        IF (loop_count = 0 OR curr_sensor.timestamp > curr_partition_limit) THEN
            curr_partition_limit := curr_sensor.timestamp + interval '1 month';
            partition_table_name := 'sensor_' || cast(curr_sensor.sensor_id as text) || '_' || to_char(date(curr_sensor.timestamp), 'YYYYMM');
            partition_index_timestamp_name := 'index_timestamp_' || cast(curr_sensor.sensor_id as text) || date(curr_sensor.timestamp);
            partition_index_id_name := 'index_id_' || cast(curr_sensor.sensor_id as text) || date(curr_sensor.timestamp);
            EXECUTE 'CREATE TABLE ' || partition_table_name || ' (CHECK (timestamp >= date(curr_sensor.timestamp) ' ||
                     'AND timestamp < (date(curr_sensor.timestamp) + interval ' || '''1 month''' || '))) ' ||
                     'INHERITS (sensor_fake_data)';
            -- add index to the new table
            EXECUTE format('CREATE INDEX %I ON %I (timestamp)', partition_index_timestamp_name, partition_table_name);
            EXECUTE format('CREATE INDEX %I ON %I (sensor_id)', partition_index_id_name, partition_table_name);
            EXECUTE format('INSERT INTO %I VALUES(curr_sensor.*)', partition_table_name);
        ELSE
            EXECUTE format('INSERT INTO %I VALUES(curr_sensor.*)', partition_table_name);
        END IF;
        loop_count := loop_count + 1;
    END LOOP;
END;
$BODY$

我不明白为什么缺少从句。一般来说，我不理解这个错误，因为我只是试图创建一个表（在第20行）并设置一些检查约束？

最佳答案

curr_sensor是plpgsql函数中的行/记录变量（所需的声明LANGUAGE plpgsql缺少btw）。
但是，您的EXECUTE试图创建一个表，它引用了一个名为CHECK的表，该表显然不存在。也不应该。
相反，传递正确引用的（！）列curr_sensor的值，用于连接正确的DDL语句。当您在函数中使用curr_sensor.timestamp来执行其他更简单的命令时，不使用它似乎很奇怪。用它来清理，并正确引用：

...
   EXECUTE format('CREATE TABLE %I (CHECK (timestamp >= %L AND timestamp < %L))
                   INHERITS (sensor_fake_data)'
                 , partition_table_name
                 , curr_sensor.timestamp::date
                 , (curr_sensor.timestamp + interval '1 month')::date);
...

可能还有更多的问题，我没有看得更远。
相关：
Error when setting n_distinct using a plpgsql variable
不管怎样，考虑升级到Postgres 10（目前是RC），它附带了闪亮的newdeclarative partitioning来替换基于继承的分区。
我最近在dba.SE上发布了一些示例代码，其中包含相关答案中的链接和解释：
Better database for “keep always the 5 latest entries per ID and delete older”?

关于postgresql - 表»rowtype«缺少FROM子句条目，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/46471784/