Problem using any operator in plpgsql function

Question

When i run this function:

CREATE OR REPLACE FUNCTION insert_styles(raw_styles text)
  RETURNS integer AS
$BODY$
declare
    arr_value TEXT[];
    upper_limit INTEGER;    
    style_ids INTEGER[];
    BEGIN
        arr_value   := string_to_array(raw_styles, ',');
        upper_limit := array_upper(arr_value, 1);

        RAISE NOTICE 'arr_value = %', arr_value;
        SELECT music_style FROM music_style INTO style_ids WHERE name = ANY (arr_value);
        RETURN upper;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

with this query:

SELECT insert_styles('Techno,ddd,wer,WJKDF');

It gives me the following error:

NOTICE:  arr_value = {Techno,ddd,wer,WJKDF}
ERROR:  array value must start with "{" or dimension information
CONTEXT:  PL/pgSQL function "insert_styles" line 10 at SQL statement

I just can't figure it out. I'am a postgres newbie!

bye!!

Grzegorz Szpetkowski · Accepted Answer · 2011-08-24 21:47:29Z

2

Assuming that your music_style column has integer datatype you just need to add array_agg aggregate into your query:

SELECT array_agg(music_style)
FROM music_style
INTO style_ids -- style_ids is integer[]
WHERE name = ANY (arr_value);

answered Aug 24, 2011 at 21:47

Grzegorz Szpetkowski

38.2k6 gold badges94 silver badges140 bronze badges

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

Collectives™ on Stack Overflow

Problem using any operator in plpgsql function

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