Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
    -- will hold the splitted raw text 
    gps_data text;

    -- 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[1]);  

    -- returning null will stop the inserting of the new record
    RETURN null;
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?

share|improve this question

closed as off-topic by dezso, Paul White, Max Vernon, RolandoMySQLDBA, Kin Apr 11 '14 at 14:34

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – dezso, Paul White, Max Vernon, RolandoMySQLDBA, Kin
If this question can be reworded to fit the rules in the help center, please edit the question.

You have to declare gps_data as text[]. – dezso Mar 21 '14 at 22:38
Argh, that was easy :( Thank you.... – stUrb Mar 21 '14 at 23:52
Aside: The assignment operator in plpgsql is :=, not =. – Erwin Brandstetter Mar 22 '14 at 12:07