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[])

facing error : ERROR: malformed array literal: "set session "myapp.user" =" LINE 1: SELECT 'set session "myapp.user" ='||$1 — user2638158
– user2638158, Commented May 17, 2017 at 11:15

Łukasz Kamiński · Accepted Answer · 2017-05-17 11:57:17Z

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;

answered May 17, 2017 at 11:57

Łukasz Kamiński

6,0301 gold badge26 silver badges33 bronze badges

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

Comments

Vao Tsun · Accepted Answer · 2017-05-18 12:21:29Z

0

you should use format for dynamic sql. (updated to reflect OP comment)

type (required)

The type of format conversion to use to produce the format specifier's output. The following types are supported:

s formats the argument value as a simple string. A null value is treated as an empty string.

I treats the argument value as an SQL identifier, double-quoting it if necessary. It is an error for the value to be null (equivalent to quote_ident).

L quotes the argument value as an SQL literal. A null value is displayed as the string NULL, without quotes (equivalent to quote_nullable).

change

 sql ='set session "myapp.user" ='||$1;
        execute  sql;

to

 sql =format('set session "myapp.user" = %L',$1);
        execute  sql;

here's working example:

t=# SELECT current_setting('myapp.user');
ERROR:  unrecognized configuration parameter "myapp.user"
Time: 0.305 ms
t=# do
t-# $$
t$# begin
t$#   execute format ('set session "myapp.user" = %L','try it');
t$# end;
t$# $$
t-# ;
DO
Time: 0.337 ms
t=# SELECT current_setting('myapp.user');
 current_setting
-----------------
 try it
(1 row)

edited May 18, 2017 at 12:21

answered May 17, 2017 at 11:51

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

1 Comment

Vao Tsun Over a year ago

@user2638158 quotes the argument value as an SQL literal.

Collectives™ on Stack Overflow

how pass array in postgresql function

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related