CODEWITHSUNDEEP
postgresql
Vyacheslav
Vyacheslav

Reputation: 27221

Syntax error at or near "unnest"

This request:

unnest('{1,2}'::int[]);

gives to me this error:

syntax error at or near "unnest"

neither unnest('{1,2}'); works

Why?

intire:

CREATE OR REPLACE FUNCTION result() RETURNS setof  users  AS
            $$
            DECLARE

            BEGIN
            unnest('{1,2}'::int[]);

RETURN QUERY SELECT * FROM   users;
            END;
            $$ LANGUAGE plpgsql;
            SELECT result();

EDIT The core idea:

To retrive and manipualate with the bigint[] which is stored inside in a column. So, i have got this:

SELECT * FROM users WHERE email = email_ LIMIT 1 INTO usr;

Then, usr.chain contains some bigint[] data. For example, {1,2,3,4,5,6,7,8,9,10}. I want to save only the 4 last of them.

How to retrieve {7,8,9,10} and {1,2,3,4,5,6} and iterate over these arrays?

I only found the solution is to use SELECT FROM unnest(usr.chain) AS x ORDER BY x ASC LIMIT (sdl - mdl) OFFSET mchain and so on. but unnest function gives to me this stupid error. I'm really do not understand why it happends. It doesn't work in sucj easy case I wrote at the beginning of the question. subarray function doesn't work because of the data type is bigint[] not int[]

Futher more, the code unnest(ARRAY[1,2]) gives to me the same error. http://www.postgresql.org/docs/9.2/static/functions-array.html The same error for array_append function

Upvotes: 0

Views: 2825

Answers (1)

LongBeard_Boldy
LongBeard_Boldy

Reputation: 812

to iterate over array:

CREATE OR REPLACE FUNCTION someresult(somearr bigint[] ) RETURNS setof     bigint  AS
$$
DECLARE
  i integer;
  x bigint;
BEGIN

for x in select unnest($1)
   loop
-- do something
   return next x;
   end loop;
 --  or 

FOR i IN array_lower($1, 1) .. array_upper($1, 1) 
    LOOP  
    -- do something like:
    return next ($1)[i];
    end loop;

 END;
$$ LANGUAGE plpgsql;

select someresult('{1,2,3,4}') ;

array_append ....

CREATE OR REPLACE FUNCTION someresult2(somearr bigint[],val bigint ) RETURNS  bigint[]  AS
$$
DECLARE
  somenew_arr bigint[];
BEGIN
somenew_arr = array_append($1, $2 );
return somenew_arr;
 END;
$$ LANGUAGE plpgsql;
select someresult2('{1,2,3,4}' ,222) ;

so, here you have basic example how to iterate and append arrays. Now can you write step by step what you want to do, to achieve .

Upvotes: 1

Related Questions