How to do multiple inserts/updates?

[tailhook]

There is quite simple way to do that for one property:

FOR value in {array_unpack(<array<str>>$values)} UNION
(INSERT Type { value := value })

But as long as I want to insert two fields this falls short. Only way that I've found is:

FOR pair in {array_unpack(<array<str>>$pairs)} UNION (
  WITH pair := (SELECT str_split(pair, ":"))
  INSERT Type { key1 := pair[0], key2 := pair[1] }
)

Which is kinda: "serialize everything into an array of strings and deserialize in a loop".

Am I missing something? (Except using JSON, which is basically the same idea).

[elprans]

This is a limitation of Postgres that we've not yet implemented a workaround for: you cannot pass tuples as query arguments. If we assume that we can pass tuples as arguments, then it's:

WITH data := <array <tuple <key1: str, key2: str> >$pairs
FOR pair IN array_unpack(data)
INSERT Type { key1 := pair.key1, key2 := pair.key2 }

at some point we can even add spreading sugar for a case like this to avoid repeating the props:

WITH data := <array <tuple <key1: str, key2: str> >$pairs
FOR pair IN array_unpack(data)
INSERT Type { ...pair }

[tailhook]

If we assume that we can pass tuples as arguments, then it's:

What stops us implementing that?

[elprans]

We need to implement argument repacking and convert incoming tuples into something Postgres can accept, most likely an array of bytea data, which we would then transparently decode in the query.

[tailhook]

I think with aiopg I could push list of dicts to postgres. The only issue what that that doesn't work with anonymous types. I.e. you can create a record type and then cast incoming array to that record type. Is there still no way to define anonymous (or temporary) record type? Or is there decode-postgres-record-from-bytes kind of function that can work for us? (I mean I don't think we want to implement another decoder for every type but using postgres functions)

[thedeadliestcatch]

Has this changed?

I recently filed #6689 which is somewhat related to this case.

[msullivan]

We have added support for tuples in arguments a while ago, so this works now:

WITH data := <array <tuple <key1: str, key2: str> >$pairs
FOR pair IN array_unpack(data) union (
  INSERT Type { key1 := pair.key1, key2 := pair.key2 }
)

[thedeadliestcatch]

That is excellent.

How can I integrate that in a nested query?

        phones := (
            WITH phone_data := <array <tuple <country_code: int16, area_code: int16, number: str, orig_number: str> >$phone_list
            FOR phone_item IN array_unpack(phone_data)
            INSERT PhoneNumber {
                country_code := phone_item.country_code,
                area_code := phone_item.area_code,
                number := phone_item.number,
                original_number := phone_item.orig_number
            }
        )

phones is a multi link for PhoneNumber in the Employee object, many to many, as phones can be assigned to multiple individuals in the PBX through ring groups (for example). Ideally also I would like it idempotent, so the insertion behaves as a multi select for every phone that already exists.