Create an Array with a dynamic Query in PL/pgSQL

Question

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;

Pavel Stehule · Accepted Answer · 2012-05-13 14:23:04Z

10

You cannot use EXECUTE inside SQL statement - EXECUTE is only plpgsql statement, so it cannot be inside SQL -

EXECUTE string INTO variable

answered May 13, 2012 at 14:23

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Veselin Over a year ago

I see, thanks. How do I create an array from an EXECUTE query than?

Pavel Stehule Over a year ago

EXECUTE 'SELECT ARRAY(SELECT mycolumn FROM mytable)' INTO avar; or in your exampleL: EXECUTE 'SELECT ARRAY(' || findAllQuery || ')' INTO ... Just note - you query is not vulnerable to sql injection - you should to use quote_ident functions for secure quoting of identifiers inside dynamic SQL

Veselin Over a year ago

Many thanks again! This is working :). I thake it you mean my query is vulnarable to injections and I should rewrite it to

findAllQuery := 'SELECT DISTINCT ' || quote_ident(mapTable2_key) ||         ' FROM '  || quote_ident(mapping_table) ||         ' WHERE ' || quote_ident(mapTable1_key) || ' = ANY(inputIdsToDelete)';

? My only problem now is to bind my input array inputIdsToDelete into the select query. I get another error like Error: Column inputIdsToDelete does not exist. Should I open anoter question or can you help me here?

Pavel Stehule Over a year ago

you can use a USAGE clause: - EXECUTE '..=ANY($1)' INTO ... USING array_variable - or string based solution: || ' = ANY(''' || inputIdsToDelete::text || ''')'

Collectives™ on Stack Overflow

Create an Array with a dynamic Query in PL/pgSQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related