PostgreSql how to declare variable type list

Question

In PostgreSql procedure, I need to create a list of integers to use in a delete statement, as in the example:

DELETE FROM appointment_virtual WHERE appointment_id IN (list_delete);

I need help with:

How do I declare a list type variable?
I found the following form, but I was unsure if it serves the purpose of the DELETE statement:

list_delete integer ARRAY;

How do I add items to this list variable?
I found the following way:

list_delete = array_append (_delete, _appointment_id);

How to zero the contents of this list variable?
Is the syntax below correct?

list_delete = [];

Thanks any help!

Did you have a read of postgresql.org/docs/current/arrays.html? — Bergi
– Bergi, Commented Oct 9, 2019 at 20:36
Also see this topic for the syntax in the WHERE clause: it's either appointment_id = ANY(list_delete) or appointment_id IN unnest(list_delete). — Bergi
– Bergi, Commented Oct 9, 2019 at 20:42
IN cannot be used with arrays. Use appointment_id = ANY(list_delete) instead. — Islingre
– Islingre, Commented Oct 9, 2019 at 20:42

Laurenz Albe · Accepted Answer · 2019-10-10 02:50:19Z

15

To define an array variable append [] to the end of the data type:

list_delete integer[];

To assign values use

list_delete := array[1,2];

to append an integer to the array:

list_delete := list_delete||4;

To assign an empty array use:

list_delete := CAST(array[] AS integer[]);

Or set it to null

list_delete := null;

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

answered Oct 9, 2019 at 20:58

user330315

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

1 Answer 1