Missing FROM-clause entry for table »rowtype«

Question

I am currently writing a function in the plpgsql language to create partitions which will hold sensor data for each month (one partition for one month and sensor). I am stuck with this error:

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

The function looks as follows:

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$

I dont understand why there is missing a FROM-Clause. In general I don't understand the Error since I am just trying to create a table (at line 20) and to set some check constraints?

There is no declaration for curr_sensorin the CREATE TABLE statement. The error is correct... — Usagi Miyamoto
@UsagiMiyamoto so I have to store curr_sensor under a variable in order to use it in a SQL-Query which is being run with the EXECUTE keyword? — bajro
There are no variables declared in the statement of the EXECUTE statement. — Usagi Miyamoto
Also, i do not think INHERITS is what you need here... I would use LIKE, as per docs: postgresql.org/docs/9.6/static/sql-createtable.html — Usagi Miyamoto
maybe you meant ' (CHECK (timestamp >= date(curr_sensor.timestamp) ' to be ' (CHECK (timestamp >= date($$'||curr_sensor.timestamp||'$$) ' and same for 'AND timestamp < (date(curr_sensor.timestamp) + interval '?.. — Vao Tsun

Erwin Brandstetter Erwin Brandstetter · Accepted Answer · 2017-10-05T05:33:26

curr_sensor is a row / record variable inside the plpgsql function (the required declaration LANGUAGE plpgsql is missing btw).

But your EXECUTE tries to create a table with a CHECK constraint referring to a table called curr_sensor, which obviously does not exists. Nor should it.

Instead, pass the properly quoted (!) value of the column curr_sensor.timestamp to concatenate the correct DDL statement. It seems odd you wouldn't use format() for this while you have it in your function for other, simpler commands. Use it to clean this up, and quote properly:

...
   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);
...

There may be more problems, I did not look much further.

Error when setting n_distinct using a plpgsql variable

Either way, consider upgrading to Postgres 10 (currently RC), which ships with shiny new declarative partitioning to replace partitioning based on inheritance.

I posted some sample code with links and explanation in this related answer on dba.SE recently:

Better database for “keep always the 5 latest entries per ID and delete older”?

Missing FROM-clause entry for table »rowtype«

1 Answers