I am trying to create an array with a dynamic selection request in the plpgsql function. Sorry, I am getting a syntax error.
ERROR: syntax error at or near "EXECUTE"
ZEILE 19: SELECT DISTINCT INTO outputIdsToDelete ARRAY( EXECUTE findA...
^
Can someone help me? 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 $
Decare
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;
source
share