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?

Why a cursor? And to be honest, just a plain SQL function with a single query using a JOIN to match both tables, would be good enough for this. Not even a need for a function, you can do it in a view. — Frank Heikens
– Frank Heikens, Commented Jan 17, 2024 at 1:40
it is not the whole function, theres updating event happening in the whole function but I just put here the one that is making an error. — butching
– butching, Commented Jan 18, 2024 at 2:52
Even then, cursors are hardly used in PostgreSQL plpgsql because plpgsql does this automatically under the hood: (However, PL/pgSQL users do not normally need to worry about that, since FOR loops automatically use a cursor internally to avoid memory problems.) — Frank Heikens
– Frank Heikens, Commented Jan 18, 2024 at 3:21

butching · Accepted Answer · 2024-01-17 01:23:18Z

4

lol, stupid escape! '',''

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

answered Jan 17, 2024 at 1:23

butching

3421 gold badge5 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: ERROR: query returned 2 columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related