创建存储过程Delete Records postgres

Question

我创建了一个删除多条记录的函数。在我们的表中包含id为uuid类型。我们得到的输入类似于id数组。

CREATE OR REPLACE FUNCTION public.deletetVersion(item_list uuid[])
RETURNS TABLE(id uuid[]) 
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
BEGIN
RETURN QUERY
DELETE FROM version WHERE id = ANY(item_list);
END; 
$BODY$;

SELECT * from deletetVersion(Array[' b6ad1,912 -e4f1-4419-831a-c70df89ffd63'，'877898f0-2f3f-4890-a658-898e35ffee3a'])

但是我得到了如下错误:

错误:function deletetversion(text[])不存在

Answer 1

这是因为

Array['b6ad1912-e4f1-4419-831a-c70df89ffd63','877898f0-2f3f-4890-a658-898e35ffee3a']

被视为文本[]

试试下面的

Array['b6ad1912-e4f1-4419-831a-c70df89ffd63'::uuid,'877898f0-2f3f-4890-a658-898e35ffee3a'::uuid] 

作为函数

的参数例如

CREATE OR REPLACE FUNCTION public.test_uuid(item_list uuid[])
RETURNS TABLE(id uuid[]) 
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
BEGIN
RETURN QUERY
SELECT item_list;
END; 
$BODY$;



SELECT * from test_uuid(Array['b6ad1912-e4f1-4419-831a-c70df89ffd63'::uuid])

删除

CREATE OR REPLACE FUNCTION public.test_uuid(item_list uuid[])
RETURNS VOID
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
BEGIN
RETURN QUERY
DELETE from tableName WHERE id = ANY(item_list);
END; 
$BODY$;

Answer 2

您的函数应该返回setof uuid-即uuid-s表-或uuid[]。我更喜欢前者。您不需要PL/pgSQL，简单的SQL就足够了。函数是:

create or replace function public.deletetVersion(item_list uuid[])
returns setof uuid language 'sql' as
$$
delete from version where id = any(item_list) returning id;
$$;

返回数组的版本稍微复杂一点:

create or replace function public.deletetVersion(item_list uuid[])
returns uuid[] language 'sql' as
$$
with t(d_id) as
(
delete from version where id = any(item_list) returning id
)
select array_agg(d_id) from t;
$$;

并且-正如@Ibrahimshamma所说-您可能需要将参数强制转换为uuid[]。