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_sensor`in the `CREATE TABLE` statement. The error is correct... — Usagi Miyamoto, Sep 28 '17 at 14:40
@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, Sep 28 '17 at 14:43
There are no variables declared in the statement of the `EXECUTE` statement. — Usagi Miyamoto, Sep 28 '17 at 14:45
Also, i do not think `INHERITS` is what you need here... I would use `LIKE`, as per docs: https://www.postgresql.org/docs/9.6/static/sql-createtable.html — Usagi Miyamoto, Sep 28 '17 at 14:48
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, Sep 29 '17 at 08:09

score 1 · Accepted Answer · answered Oct 05 '17 at 05:33

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”?

This was indeed the problem. I changed the `EXECUTE` query by using the built-in `format()` function and now it works. A very simple and dumb mistake, but I was confused by the error messages and therefore I couldn't figure out what exactly the problem was. — bajro, Oct 08 '17 at 18:09

Missing FROM-clause entry for table »rowtype«

1 Answers1