I'm trying to write a function used by an trigger
BEFORE INSERT on a table; the function should convert a raw GPS string to useable columns in a different table.
The point where I get stuck is the type declaration of the splitted string. The function:
CREATE OR REPLACE FUNCTION convert_gps_gprmc() RETURNS trigger AS $BODY$ DECLARE -- will hold the splitted raw text gps_data text; BEGIN -- raw is the column which holds the raw gps data -- and which is inserted and triggers the trigger gps_data = regexp_split_to_array(NEW.raw, ','); -- insert the first element of the gps_data array in the 'test' column -- this results in an error because gps_data is not an array see error -- underneath INSERT INTO gps_positie (test) VALUES (gps_data); -- returning null will stop the inserting of the new record RETURN null; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100;
The creating of the function succeeds, but when I insert a new record on the raw-table it results in the following error:
ERROR: cannot subscript type character varying because it is not an array
How can I change my function to allow this operation?