1

I would like to do the following (written in some kind of simplified pseudo code) in just one PL/PGSQL function inside a Postgres 12 database:

SELECT numcolA, numcolB FROM myitemtable INTO _unused_items_array;

FOR EACH _numcol1, _numcol2 IN SELECT num_col1, num_col2 FROM some_items_table LOOP

    IF CONTAINS(_unused_items_array, [_numcol1, _numcol2]) THEN
        REMOVE_ARRAY_ITEM_IF_EXISTS(_unused_items_array, [_numcol1, _numcol2]);
        -- following lines containing REMOVE_ARRAY_ITEM_IF_EXISTS are simplified
        -- they will be in real life much more difficult to calculate
        REMOVE_ARRAY_ITEM_IF_EXISTS(_unused_items_array, [_numcol1 - 1, _numcol2 - 1]);
        REMOVE_ARRAY_ITEM_IF_EXISTS(_unused_items_array, [_numcol1 + 3, _numcol2 + 3]);
    END IF;

END LOOP

SELECT numcolA, numcolB FROM myitemtable INTO _used_items_array;
FOR EACH _unused_item IN _unused_items_array
    REMOVE_ARRAY_ITEM_IF_EXISTS(_used_items_array, _unused_item);

It can not be done just in SQL because the calculation (comment above) is much more complex than written here.

So, does anybody have an idea how I can solve it with PL/PGSQL – because the array functions in PL/PGSQL are driving me crazy in solving this (in C# it would be done in a second - but no option).

Addition (edit) As requested I add some sample data to explain the requirement:

myitemtable contains (amongst others) the following columns:

+ -------------- + -------------- +
| numcolA        | numcolB        |
+ -------------- + -------------- +
| 1              | 1              |
+ -------------- + -------------- +
| 1              | 2              |
+ -------------- + -------------- +
| 2              | 5              |
+ -------------- + -------------- +
| 2              | 9              |
+ -------------- + -------------- +

some_items_table contains (amongst others) the following columns:

+ -------------- + -------------- +
| num_col1       | num_col2       |
+ -------------- + -------------- +
| 1              | 2              |
+ -------------- + -------------- +
| 2              | 9              |
+ -------------- + -------------- +

Within the if condition there will be some other items 'virtually calculated', lets say (1|1), (1|3), (2|2), (2|8), (2|10), (3|9) and it will be checked whether these items are in _unused_items_array (and will be removed if they are - and some are, as you can see).

This all will lead at the end to an _used_items_array which will contain only item (2|5) in this example.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
DevNewBee
  • 11
  • 3
  • 2
    More often than not using arrays is a sign of a questionable database design. Properly normalized models usually don't require this. Maybe if you take a step back and explain us the underlying problem you are trying to solve (by providing sample data and expected results) we could come up with set base, relational solution that doesn't require the use of arrays to begin with –  Nov 10 '20 at 11:42
  • Does `[_numcol1 + 3, _numcol2 + 3]` indicate the indexes of the elements in the array that should be be removed or the _values_ to be removed? What data type are the array elements? –  Nov 10 '20 at 11:48
  • Thanks for your reply. I understand your remark about the db design, but be sure: Not the case over here. The requirement of the `REMOVE_ARRAY_ITEM_IF_EXISTS` is that the element will be removed. – DevNewBee Nov 10 '20 at 14:38
  • I've edited the description of the problem and added (as requested) some sample data and expected result. – DevNewBee Nov 10 '20 at 14:50
  • So do you have your answer? – Erwin Brandstetter Nov 20 '20 at 05:11

1 Answers1

1

There are many ways to do this, a plain SQL DELETE being possibly the "simplest":

DELETE FROM myitemtable m
USING  some_items_table s
WHERE (m.numcolA , m.numcolB) IN (
      (s.num_col1, s.num_col2)  -- row values from some_items_table
    , (1,1), (1,3), (2,2), (2,8), (2,10), (3,9) -- "virtually calculated" row values
      );

db<>fiddle here

This is comparing ROW values. Don't forget that NULL values don't compare equal in such a test. Related:

You can nest the statement in a PL/pgSQL function if required.

Involving arrays doesn't seem necessary. XY problem? It's simple enough to achieve the same with arrays, too, though.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Allow me, How come (2,5) got deleted? seem like I vote your answer, you can not grow score. I guess I voted too many of your answers. – jian Feb 17 '22 at 16:28
  • @Mark `(2,5)` is the only *surviving* row in the example. About reputation see: https://meta.stackexchange.com/a/7238/169168 – Erwin Brandstetter Feb 17 '22 at 16:37
  • I am sorry. reading too much of your fine answers..... brain hurts now, i guess. – jian Feb 17 '22 at 16:45