how pass array in postgresql function

Question

i am trying to pass an text array in pg function but i am facing different errors please help me how i pass an array which have different data type as text and integer.... my code here

CREATE or replace FUNCTION get_data(text[])
RETURNS TABLE(sheet_no text,type text,road_name text,lon text,lat text) 
AS $$  
DECLARE 
sql text;
 BEGIN 
        sql ='set session "myapp.user" ='||$1;
        execute  sql;

        update tbl_encroachment
        set landuse = $2 
        where tbl_encroachment.unique_land_parcel_no = $3
        and tbl_encroachment.parcel_sub_division = $4;  

        RETURN QUERY 
        select foo.sheet_no,foo.type,foo.road_name,foo.lon,foo.lat
        from tbl_encroachment as foo
        where foo.unique_land_parcel_no = $3
        and foo.parcel_sub_division = $4;
        RETURN;        

 end
$$ LANGUAGE plpgsql;

--execute query     
select sheet_no,type,road_name,lon,lat from get_data('{7,hasilpur,7-35-251-0001_1-1-9-SUK_001,9}'::text[])

Answer 1

You can't access array elements like that. $1 represents whole array, so you need to do something like $1[1], $1[2], $1[3] instead of $1, $2, $3

So try this:

CREATE or replace FUNCTION get_data(text[])
RETURNS TABLE(sheet_no text,type text,road_name text,lon text,lat text) 
AS $$  
DECLARE 
sql text;
 BEGIN 
        sql ='set session "myapp.user" ='||$1[1];
        execute  sql;

        update tbl_encroachment
        set landuse = $1[2]
        where tbl_encroachment.unique_land_parcel_no = $1[3]
        and tbl_encroachment.parcel_sub_division = $1[4];  

        RETURN QUERY 
        select foo.sheet_no,foo.type,foo.road_name,foo.lon,foo.lat
        from tbl_encroachment as foo
        where foo.unique_land_parcel_no = $1[3]
        and foo.parcel_sub_division = $1[4];
        RETURN;        

 end
$$ LANGUAGE plpgsql;