CODEWITHSUNDEEP
sqlpostgresqlplpgsql
DevNewBee
DevNewBee

Reputation: 11

Postgres: Select multiple columns and compare with array

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.

Upvotes: 1

Views: 635

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656331

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.

Upvotes: 1

Related Questions