'Declare an array in plpgsql and use it

I'm trying to do 3 actions (2 delete and 1 update ) on an array of ent_id. This array is build in my script in the select.

CREATE
    OR REPLACE FUNCTION run_script()
    RETURNS VOID
AS
$$
DECLARE
    all_ent bigint[];
BEGIN
    select ent_id
    into all_ent
    from entretiens ent
    where ent.ent_statut = 'T';

    RAISE INFO 'Start';

    delete
    from documents
    where ent_id in (all_ent);

    delete
    from comite
    where ent_id in (all_ent);

    update entretiens ent
    set ent_statut = 'N'
    where ent_id in (all_ent);

    RAISE INFO 'End';

END
$$
    LANGUAGE plpgsql;

SELECT run_script();

When I run the script I have this error :

ERROR: malformed array literal: "535030"
Détail : Array value must start with "{" or dimension information.

Any suggestion ?

arrayspostgresqlplpgsql


Solution 1:[1]

Set to array selected field values:

select ARRAY(
    select ent_id
    from entretiens ent
    where ent.ent_statut = 'T'
) into all_ent;

Use array on queries:

delete
from documents
where ent_id in (select aaa from unnest(all_ent) tb(aaa));

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ramin Faracov

Related Questions