Elixir: PostgreSQL のデータを更新 (Update)

Julia のプログラムを、ChatGPT と Gemini に渡して得られた結果です。

## Julia のプログラム

```jl:postgre_update.jl
#
#	postgre_update.jl
#
#						Mar/16/2024
# --------------------------------------------------------------------
using LibPQ
using DotEnv
# --------------------------------------------------------------------
println("*** 開始 ***")
key_in = ARGS[1]
population_in = ARGS[2]
println(key_in,'\t',population_in)
#
cfg=DotEnv.config()
user=cfg["user"]
password=cfg["password"]
data_base=cfg["data_base"]
#
today = Libc.strftime("%F", time())
println(today)
#
str_connect = "dbname= " * data_base * " host=127.0.0.1 user=scott password=tiger123 port=5432"
conn = LibPQ.Connection(str_connect)
#
command = "update cities" * " set population = " * population_in
command *= " , date_mod = '" * today * "'"
command *= " where id = '" * key_in * "'"
println(command)
rr = execute(conn, command)
println(rr)
#
close(conn)
#
println("*** 終了 ***")
# --------------------------------------------------------------------
```

## ChatGPT が作成したプログラム

```exs:lib/postgre_update.ex
# postgre_update.exs

# --------------------------------------------------------------------
defmodule PostgreUpdate do
  def main(args) do
    IO.puts("*** 開始 ***")
    [key_in | population_in] = args
    key_in = String.strip(key_in)
    population_in = String.strip(population_in)
    IO.puts("#{key_in}\t#{population_in}")

    {:ok, _} = Dotenv.Config.load()

    user = System.get_env("user")
    password = System.get_env("password")
    data_base = System.get_env("data_base")

    today = :calendar.local_time() |> :calendar.format!("{YYYY}-{0M}-{0D}")
    IO.puts(today)

    str_connect = "dbname=#{data_base} host=127.0.0.1 user=#{user} password=#{password} port=5432"
    {:ok, conn} = LibPQ.connect(str_connect)

    command = "update cities set population = #{population_in}"
    command = command <> " , date_mod = '#{today}'"
    command = command <> " where id = '#{key_in}'"
    IO.puts(command)

    {:ok, _} = LibPQ.execute(conn, command)

    LibPQ.close(conn)

    IO.puts("*** 終了 ***")
  end
end

# --------------------------------------------------------------------
PostgreUpdate.main(System.argv())
```

```exs:mix.exs
（省略）
	{:ecto, "~> 3.11"},
    {:dotenv, "~> 3.1"},
（省略）
```

実行結果

```text
$ mix run main t3464 3786
Compiling 1 file (.ex)
*** 開始 ***

== Compilation error in file lib/postgre_update.ex ==
** (FunctionClauseError) no function clause matching in String.trim/1    
    
    The following arguments were given to String.trim/1:
    
        # 1
        ["3786"]
    
    Attempted function clauses (showing 1 out of 1):
    
        def trim(string) when is_binary(string)
    
    (elixir 1.16.2) lib/string.ex:1293: String.trim/1
    lib/postgre_update.ex:9: PostgreUpdate.main/1
    lib/postgre_update.ex:38: (file)
```

## Gemini が作成したプログラム

```exs:lib/postgre_update.ex
defmodule PostgresUpdate do

  # Read environment variables using Application.get_env
  @user Application.get_env(:my_app, :user)
  @password Application.get_env(:my_app, :password)
  @database Application.get_env(:my_app, :database)

  def main(key_in, population_in) do
    IO.puts("*** 開始 ***")
    IO.puts("#{key_in}\t#{population_in}")

    # Establish connection using Ecto.Adapters.Postgres
    {:ok, conn} = Ecto.Adapters.Postgres.connect(
      database: @database,
      user: @user,
      password: @password,
      hostname: "localhost",
      port: 5432
    )

    # Prepare the update query using Ecto.Query
    query = Ecto.Query.update(Cities, i: :id)
    query = query |> Ecto.Query.set([population: population_in, date_mod: Ecto.DateTime.utc]), [i: key_in]

    # Execute the query and handle results
    {:ok, _} = Ecto.Repo.update(conn, query)
    IO.puts("Record updated successfully.")

    # Close the connection
    Ecto.Adapters.Postgres.disconnect(conn)

    IO.puts("*** 終了 ***")
  end
end

# Example usage
key_in = "123"
population_in = 100000
PostgresUpdate.main(key_in, population_in)
```

```exs:mix.exs
（省略）
	{:ecto, "~> 3.11"},
    {:dotenv, "~> 3.1"},
（省略）
```

実行結果

```text
$ mix run main t3466 1378
Compiling 1 file (.ex)

== Compilation error in file lib/postgre_update.ex ==
** (SyntaxError) invalid syntax found on lib/postgre_update.ex:23:107:
    error: unexpectedly reached end of line. The current expression is invalid or incomplete
    │
 23 │     query = query |> Ecto.Query.set([population: population_in, date_mod: Ecto.DateTime.utc]), [i: key_in]
    │                                                                                                           ^
    │
    └─ lib/postgre_update.ex:23:107
    (elixir 1.16.2) lib/kernel/parallel_compiler.ex:428: anonymous fn/5 in Kernel.ParallelCompiler.spawn_workers/8
```

## 私が作成したプログラム

```exs:lib/postgre_update.ex
defmodule PostgreUpdate do

  def main([]) do
    IO.puts "*** 開始 ***"
    [id_in,population_in] = System.argv()
    IO.puts id_in
    IO.puts population_in
#
    host = "localhost"
    user = "scott"
    password = "tiger123"
    database = "city"

    {:ok, pid} = Postgrex.start_link(hostname: host, username: user, password: password, database: database)
#
    date_mod = Date.utc_today()
    command = "UPDATE cities SET population='#{population_in}', DATE_MOD='#{date_mod}' WHERE ID = '#{id_in}'"
    IO.puts command
    Postgrex.query!(pid, command, [])
#
    aa = Postgrex.query!(pid, "SELECT * FROM cities", [])

    IO.inspect aa
#
    IO.puts "*** 終了 ***"
  end

end
```

```exs:mix.exs
（省略）
	{:postgrex, "~> 0.17.5"},
（省略）
```

実行結果

```text
$ mix run -e "PostgreUpdate.main([])" t3467 823000
*** 開始 ***
t3467
823000
UPDATE cities SET population='823000', DATE_MOD='2024-03-20' WHERE ID = 't3467'
%Postgrex.Result{
  command: :select,
  columns: ["id", "name", "population", "date_mod"],
  rows: [
    ["t3461", "広島", 32561, ~D[2013-04-30]],
    ["t3462", "福山", 81295, ~D[2013-08-10]],
    ["t3464", "呉", 73612, ~D[2013-02-09]],
    ["t3465", "尾道", 52391, ~D[2013-08-04]],
    ["t3466", "竹原", 95187, ~D[2013-01-21]],
    ["t3468", "大竹", 85791, ~D[2013-11-26]],
    ["t3469", "府中", 24139, ~D[2013-10-15]],
    ["t3467", "三次", 823000, ~D[2024-03-20]]
  ],
  num_rows: 8,
  connection_id: 25393,
  messages: []
}
*** 終了 ***
```