1

I'm trying to implement a standard table partitioning scheme in postgres but I end up with this error.

Using postgres 9.4 and postgis 2.1 on centos 7

Tables:

create table experiments (
    id serial,
    value int
);

create table nada_neurons (
    id serial,
    experiment_id int,
    geometry geometry(MultiPointZ)
);

functions:

CREATE OR REPLACE FUNCTION
public.create_partitions()
RETURNS TRIGGER AS
$BODY$
BEGIN

EXECUTE 'CREATE TABLE public.nada_neurons_' || LASTVAL() || ' (CHECK ( experiment_id = ' || LASTVAL() || ')) INHERITS (public.nada_neurons)';
EXECUTE 'CREATE INDEX neurons_experiment_id_' || LASTVAL() || ' on public.nada_neurons_' || LASTVAL() || ' USING btree(experiment_id)';
EXECUTE 'CREATE INDEX neurons_geometry_' || LASTVAL() || ' on public.nada_neurons_' || LASTVAL() || ' USING gist(geometry gist_geometry_ops_nd)';

RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER add_experiment_trigger
AFTER INSERT ON public.experiments
FOR EACH ROW EXECUTE PROCEDURE public.create_partitions();


CREATE OR REPLACE FUNCTION neurons_insert_function()
RETURNS TRIGGER AS $$
BEGIN
    EXECUTE 'INSERT INTO nada_neurons_' || NEW.experiment_id || ' VALUES ' || NEW.* || ''; 
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_neurons_trigger
    BEFORE INSERT ON nada_neurons
    FOR EACH ROW EXECUTE PROCEDURE neurons_insert_function();

The insert:

postgres=# insert into nada_neurons(experiment_id, geometry) values(5, 'MULTIPOINT(0 0 0, 5 5 5)');
ERROR:  column "geometry" is of type geometry but expression is of type numeric
LINE 1: INSERT INTO nada_neurons_5 VALUES (8,5,010400008002000000010...
                                               ^
HINT:  You will need to rewrite or cast the expression.
QUERY:  INSERT INTO nada_neurons_5 VALUES (8,5,01040000800200000001010000800000000000000000000000000000000000000000000000000101000080000000000000144000000000000014400000000000001440)
CONTEXT:  PL/pgSQL function neurons_insert_function() line 3 at EXECUTE statement

EDIT: looking at kttii's response below, this blog post by Paul Ramsey and this stackoverflow question I believe the core issue is that the EXECUTE statement causes the PostGIS constructor to be evaluated. This would be fine if it then enclosed the result in quotes, but since it doesn't I need to find either: - A method of getting the EXECUTE to ignore a section of the string. - A different technique for managing the insert trigger.

EDIT 2: Here is how I solved this issue, and included the correct usage of the format() and USING features

CREATE OR REPLACE FUNCTION neurons_insert_function()
RETURNS TRIGGER AS $$
DECLARE
    sql_stmt varchar(200);
    r nada_neuron%ROWTYPE; 
BEGIN
    EXECUTE format('INSERT INTO nada_neuron_' || NEW.experiment_id 
    || ' (name, cell_type, geometry, keypoint, experiment_id, layer_id) ' 
    || ' SELECT $1, $2, $3, $4, $5, $6 ' )
    USING NEW.name, NEW.cell_type, NEW.geometry::text, NEW.keypoint::text, NEW.experiment_id, NEW.layer_id; 
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS insert_neurons_trigger ON public.nada_neuron;
CREATE TRIGGER insert_neurons_trigger
    INSTEAD OF INSERT ON nada_neuron
    FOR EACH ROW EXECUTE PROCEDURE neurons_insert_function();

1 Answer 1

4

the error is telling you that the statement failed because you tried to pass a number rather than a geometry:

INSERT INTO nada_neurons_5
VALUES (8,5,01040000800200000001010000800000000000000000000000000000000000000000000000000101000080000000000000144000000000000014400000000000001440)

the geometry should have been enclosed in quotes:

INSERT INTO nada_neurons_5
VALUES (8,5,'01040000800200000001010000800000000000000000000000000000000000000000000000000101000080000000000000144000000000000014400000000000001440')

You'll need to change your execute statement to be more specific rather than using New.* so that you can pass more specific/casted values as such:

EXECUTE 'INSERT INTO nada_neurons_' || NEW.experiment_id || ' 
         VALUES (' || NEW.experiment_id || ',''' || NEW.geometry || ''')';

or maybe:

EXECUTE 'INSERT INTO nada_neurons_' || NEW.experiment_id || '
        VALUES (' || NEW.experiment_id || ',''' || NEW.geometry || ''':geometry)';
4
  • Thanks, that makes a lot of sense. Do you have an idea how to format the original insert statement so that it keeps the value in quotes? insert into nada_neurons(experiment_id, geometry) values(5, 'MULTIPOINT(0 0 0, 5 5 5)');
    – jfarr
    Aug 2, 2016 at 13:31
  • @jfarr The error mentions casting... what about this: INSERT INTO nada_neurons(experiment_id, geometry) VALUES (5, 'MULTIPOINT(0 0 0, 5 5 5)'::geometry);
    – kttii
    Aug 3, 2016 at 12:54
  • @jfarr I would change your execute statement to be more specific rather than using New.* so that you can pass more specific/casted values as such: EXECUTE 'INSERT INTO nada_neurons_' || NEW.experiment_id || ' VALUES (' || NEW.experiment_id || ',''' || NEW.geometry || ''')'; or maybe: EXECUTE 'INSERT INTO nada_neurons_' || NEW.experiment_id || ' VALUES (' || NEW.experiment_id || ',''' || NEW.geometry || ''':geometry)';
    – kttii
    Aug 3, 2016 at 19:22
  • That's exactly how I got this fixed. I'm going to edit my original post with a function that works. I wanted to also update it to use format(), but I won't have time to work on this area for a while.
    – jfarr
    Aug 3, 2016 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.