PostgreSQL: ERROR: query returned 2 columns

Question

My function is like this:

CREATE OR REPLACE FUNCTION insert_o_record_desc()
     RETURNS text
     LANGUAGE 'plpgsql'
AS $$

DECLARE
o_record TEXT DEFAULT '';
rec_o_record RECORD;
cur_o_record CURSOR FOR SELECT * FROM o_record_fact;
v_spent_desc varchar(255) DEFAULT NULL;
    
BEGIN
-- Open the cursor
OPEN cur_o_record;
LOOP
-- Fetch row into the person
    FETCH cur_o_record INTO rec_o_record;
-- Exit when no more row to fetch
    EXIT WHEN NOT FOUND;
    
    EXECUTE 'SELECT string_agg(value_label, ', ') as vlabel FROM spent_table WHERE code ~* $1'
    INTO v_spent_desc
    USING regexp_replace(TRIM(rec_o_record.spent_column), '(.)(?!$)','\1|','g');
END LOOP;
-- Close the cursor
CLOSE cur_o_record;
RETURN o_record;
END;
$$;

select insert_o_record_desc();

Then I got an error with this message

ERROR: query returned 2 columns
CONTEXT: query: 'SELECT string_agg(value_label1, ', ') as vlabel FROM spent_table WHERE code ~* $1'
PL/pgSQL function insert_o_record_desc() line 123 at EXECUTE
SQL state: 42601

but when i just run this query

SELECT string_agg(value_label, ', ') as vlabel FROM spent_table WHERE code ~* 'A|B|C|E';

I got result with one column. why is that?

Answer 1

lol, stupid escape! '',''

    EXECUTE 'SELECT string_agg(value_label,'','') as vlabel FROM spent_table WHERE code ~* $1'