Take the 2-minute tour ×
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. It's 100% free, no registration required.

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


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

share|improve this question

closed as off-topic by dezso, Paul White, Max Vernon, RolandoMySQLDBA, Kin Apr 11 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.

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