I am trying to create an array with a dynamic select query in a plpgsql function. Unfortunately I get an syntax error.
ERROR: syntax error at or near "EXECUTE"
ZEILE 19: SELECT DISTINCT INTO outputIdsToDelete ARRAY( EXECUTE findA...
^
Can somebody help me please? Here is the function itself:
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;