如何在PostgreSQL函数中传递数组

Question

我正在尝试在pg函数中传递一个文本数组，但我遇到了不同的错误，请帮助我如何传递一个包含文本和整数等不同数据类型的数组....我的代码如下：

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

- user2638158

---

面对错误：ERROR: 无效的数组字面量："set session "myapp.user" =" 第1行：SELECT 'set session "myapp.user" ='||$1 - user2638158

Answer 1

你不能这样访问数组元素。 $1 代表整个数组，所以你需要像这样做：$1[1]、$1[2]、$1[3]，而不是 $1、$2、$3。

所以尝试这样做：

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;

- Łukasz Kamiński

Answer 2

在动态sql中应该使用format。 （已更新以反映 OP 评论）

类型（必填）

用于生成格式说明符输出的格式转换类型。支持以下类型：

• s 将参数值格式化为简单字符串。null 值将被视为空字符串。

• I 将参数值视为 SQL 标识符，必要时会将其双引号括起来。如果值为 null，则是错误（等同于 quote_ident）。

• L 将参数值作为 SQL 字面量引用。null 值将显示为字符串 NULL，不带引号（等同于 quote_nullable）。

更改

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

到

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

这里是一个可用的示例：

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)

- Vao Tsun

---

@user2638158 将参数值引用为 SQL 文字。 - Vao Tsun