This started as a question about indexes, but then when I got my answer I realized I would have to try and convert the answer into 8.4 syntax (I can't believe that format does not exist).
I thought I was handling the variables correctly, but for some reason I can't get the typing to function properly:
CREATE OR REPLACE FUNCTION add_column(schema_name TEXT, table_name TEXT, column_name TEXT, data_type TEXT) RETURNS BOOLEAN AS $BODY$ DECLARE _tmp text; BEGIN EXECUTE 'SELECT COLUMN_NAME FROM information_schema.columns WHERE table_schema='''+|| schema_name+''' AND table_name='''+||table_name+''' AND column_name='''+||column_name+'''' INTO _tmp; IF _tmp IS NOT NULL THEN RAISE NOTICE USING MESSAGE = 'Column '+||column_name+' already exists in '+||schema_name+'.'+||table_name; RETURN FALSE; END IF; EXECUTE format('ALTER TABLE %I.%I ADD COLUMN %I %s;', schema_name, table_name, column_name, data_type); RAISE NOTICE USING MESSAGE = 'Column '+||column_name+' added to '+||schema_name+'.'+||table_name; RETURN TRUE; END; $BODY$ LANGUAGE 'plpgsql';
Here is the error message I get:
ERROR: operator does not exist: text + unknown
Does anyone know how I can get this to work in 8.4?