Postgresql 函数循环遍历输入参数并追加到 hstore

Question

我想知道在 PostgreSQL 中是否可以遍历当前函数的所有可用输入参数并将这些参数 (key=>value) 附加到 hstore 变量。

hstore-key = 给定输入参数/参数的名称....
hstore-Value = 给定参数的值...

例如:

-- input for call function with input arguments
SELECT append_hstore_from_args ('val1','val22','val333');

CREATE OR REPLACE FUNCTION append_hstore_from_args (IN param1 text, IN param2 text, IN param3 text)
RETURNS text AS
$BODY$ 
DECLARE   
new_h hstore;    
BEGIN
-- below is pseudo
for p in all_params
loop 
    new_h := new_h+"$p->name"=>"$p->value";
end loop;

-- at this point the variable new_h (hstore) should contain 
-- '"param1"=>"val1","param2"=>"val22","param3"=>"val333"'

-- call function with hstore argument
perform test.func123(new_h);

RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER    

我进行了搜索，但遗憾的是没有找到有关如何在 PostgreSQL 中完成此操作的任何提示。

Answer 1

我假设这是一个如何处理 variable number of function arguments 的问题.

可变参数可以在函数内部作为数组访问。您可以使用 FOREACH ... LOOP :

create or replace function strings_to_hstore(args variadic text[])
returns hstore language plpgsql as $$
declare
    idx int = 0;
    str text;
    hst hstore;
    res hstore = '';
begin
    foreach str in array args loop
        idx:= idx+ 1;
        execute format($f$select 'param%s=>%s'$f$, idx, str) into hst;
        res:= res|| hst;
    end loop;
    return res;
end $$;

select strings_to_hstore('one', 'two');

            strings_to_hstore         
----------------------------------
 "param1"=>"one", "param2"=>"two"
(1 row)

select strings_to_hstore('red', 'green', 'blue');

                  strings_to_hstore                   
------------------------------------------------------
 "param1"=>"red", "param2"=>"green", "param3"=>"blue"
(1 row)

如果您想在参数列表中定义 hstore 键:

create or replace function strings_with_keys_to_hstore(args variadic text[])
returns hstore language plpgsql as $$
declare
    idx int = 0;
    key text;
    str text;
    hst hstore;
    res hstore = '';
begin
    foreach str in array args loop
        idx:= idx+ 1;
        if idx & 1 then
            key:= str;
        else
            execute format($f$select '%s=>%s'$f$, key, str) into hst;
            res:= res|| hst;
        end if;
    end loop;
    return res;
end $$;

select strings_with_keys_to_hstore('key1', 'val1', 'key2', 'val2');

  strings_with_keys_to_hstore   
--------------------------------
 "key1"=>"val1", "key2"=>"val2"
(1 row)

请注意，有 standard hstore functions : hstore(text[]) 和 hstore(text[], text[])。