SQL Query to Substitute value in the scanned results and update that field of Table

Question

I have a 1 to many Organization: Users relationship. I want to fetch the usernames of all User model of an Organization, capture a part of that username and append/substitute it with new value.

Here is how I am doing:

1. Form the raw SQL to Get the matching usernames and replace them with new value.

raw = "SELECT REGEXP_REPLACE($1::string[], '(^[a-z0-9]+)((\s[a-z0-9]+))*\@([a-z0-9]+)$', m.name[1] || '@' || $2) FROM (SELECT REGEXP_MATCHES($1::string[], '(^[a-z0-9]+)((\s[a-z0-9]+))*\@([a-z0-9]+)$') AS name) m"

2. Get the matching usernames and replace them with new value. usernames: list of usernames retrieved from queryable

Repo.query(raw, [usernames, a_string])

3. Error I am getting

   SELECT REGEXP_REPLACE($1::string[], '(^[a-z0-9]+)(( [a-z0-9]+))@([a-z0-9]+)$', m.name[1] || '@' || $2) FROM (SELECT REGEXP_MATCHES($1::string[], '(^[a-z0-9]+)(( [a-z0-9]+))@([a-z0-9]+)$') AS name) m [["tradeboox@trdbx18"], "trdbx17"] {:error, %Postgrex.Error{connection_id: 7222, message: nil, postgres: %{code: :undefined_object, file: "parse_type.c", line: "257", message: "type \"string[]\" does not exist", pg_code: "42704", position: "137", routine: "typenameType", severity: "ERROR", unknown: "ERROR"}}}

FYI: The username field of User model is of type citext

---

4. Once I get the replaced values, I want to update the User with something like

update([u], set: [username: new_values])

Any ideas on how to proceed with this?

`

Answer 1

There is no string type in PostgreSQL.

Function regexp_matches accepts as first parameter only text and it can't be array. So what you need to do is first change that type to text, then unnest($1::text[]) your array. Iterate over resulting set of rows with those regexp.

raw = "SELECT REGEXP_REPLACE(m.item, '(^[a-z0-9]+)((\s[a-z0-9]+))*\@([a-z0-9]+)$', m.name[1] || '@' || $2)
         FROM (
               SELECT item, REGEXP_MATCHES(item, '(^[a-z0-9]+)((\s[a-z0-9]+))*\@([a-z0-9]+)$') AS name
                 FROM unnest($1::text[]) AS items(item)
              ) m"

If I understand it correctly, you are trying to replace everything after @ with some different string - if that is the case, then your regexp will put anything after spacebar into second element of matches array. You would need this instead: ((^[a-z0-9]+)(\s[a-z0-9]+)*).

If above is true, then you can do all that much easier with this:

SELECT REGEXP_REPLACE(item, '((^[a-z0-9]+)(\s[a-z0-9]+)*)\@([a-z0-9]+)$', '\1' || $2) AS name
  FROM unnest($1::text[]) AS items(item)

Best practice however is to simply do replace in UPDATE statement:

UPDATE "User" SET
  name = concat(split_part(name, '@', 1), '@', $2)
WHERE organization_id = $3
  AND name ~* '^[a-z0-9]+(\s[a-z0-9]+)*\@[a-z0-9]+$'

It will split name by @, take first part, then append @ and whatever is assigned to $2 (domain name I guess). It will update only rows that have organization_id matching to some id and have names matching your regexp (you can omit regexp if you want to change all names from organization). Make sure that table in actually named User, case sensitive, or remove double quotes to have case insensitive version.

I sadly do not know how to do this in your ORM.