CODEWITHSUNDEEP
functionpostgresqlstored-proceduresplpgsql
Emanuele Mazzoni
Emanuele Mazzoni

Reputation: 716

Error on execute postgresql function

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?

Upvotes: 1

Views: 1245

Answers (1)

klin
klin

Reputation: 121604

'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;

Upvotes: 1

Related Questions