Error on execute postgresql function

Question

I have this simple function:

CREATE OR REPLACE FUNCTION soundavity.perform_search_by_serie_name( in_search_text   text )
RETURNS bigint[] AS

$BODY$

DECLARE 
match_id bigint[];  

BEGIN

SELECT id INTO match_id
FROM soundavity.tv_serieslist_tbl
where id IN (   
        SELECT id 
        FROM table
        WHERE to_tsvector('english', name) @@ to_tsquery('english', in_search_text)
        )
LIMIT 10;   


RETURN match_id;

END;

But when i try to

select perform_search_by_serie_name('something');

Postgres returns:

ERROR:  array value must start with "{" or dimension information
CONTEXT:  PL/pgSQL function soundavity.perform_search_by_serie_name(text) line 8 at    SQL statement

Where is the error?

klin · Accepted Answer · 2013-03-06 11:49:56Z

1

'SELECT id' returns setof bigint not bigint array. Try this:

DECLARE 
    match_id bigint[] = '{}';
    rid bigint;
BEGIN
    for rid in
        SELECT ... -- without INTO
    loop
        match_id:= array_append(match_id, rid);
    end loop;
RETURN match_id;

answered Mar 6, 2013 at 11:49

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Error on execute postgresql 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