数组 – 在PL / pgSQL中创建一个带有动态查询的数组
发布时间:2020-12-13 16:03:46 所属栏目:百科 来源:网络整理
导读:我试图在plpgsql函数中创建一个带有动态选择查询的数组.不幸的是我收到了语法错误. ERROR: syntax error at or near "EXECUTE"ZEILE 19: SELECT DISTINCT INTO outputIdsToDelete ARRAY( EXECUTE findA... ^ 有人可以帮帮我吗? 这是函数本身: CREATE OR RE
我试图在plpgsql函数中创建一个带有动态选择查询的数组.不幸的是我收到了语法错误.
ERROR: syntax error at or near "EXECUTE" ZEILE 19: SELECT DISTINCT INTO outputIdsToDelete ARRAY( EXECUTE findA... ^ 有人可以帮帮我吗? CREATE OR REPLACE FUNCTION deleteMAPPINGs( mapTable1_key text,mapping_table text,mapTable2_key text,table2 text,table2_key text,inputIdsToDelete bigint[]) RETURNS bigint[] AS $BODY$ DECLARE outputIdsToDelete bigint[]; findAllQuery text; findUnreferencedQuery text; BEGIN findAllQuery := 'SELECT DISTINCT ' || mapTable2_key || ' FROM ' || mapping_table || ' WHERE ' || mapTable1_key || ' = ANY(inputIdsToDelete)'; findUnreferencedQuery := 'SELECT DISTINCT ' || table2_key || --find unused ' FROM ' || table2 || ' WHERE ' || table2_key || ' NOT IN (' || 'SELECT DISTINCT ' || mapTable2_key || --all used ' FROM ' || mapping_table || ')'; SELECT DISTINCT INTO outputIdsToDelete ARRAY( EXECUTE findAllQuery ); DELETE FROM mapping_table WHERE mapTable1_key = ANY(inputIdsToDelete) AND mapTable2_key = ANY(outputIdsToDelete); SELECT DISTINCT INTO outputIdsToDelete --overwrite with unused ARRAY(EXECUTE findUnreferencedQuery); RETURN outputIdsToDelete; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; 解决方法
你不能在SQL语句中使用EXECUTE – EXECUTE只是plpgsql语句,所以它不能在SQL里面 –
EXECUTE string INTO variable (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |