current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite
1

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

'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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.