-module(pq).

-compile([export_all, nowarn_export_all]).

%% postgresql queries module

%% without pool
% get_all_cities(Mpid)
% update_city_by_id(Mpid, City_Id, City_Name, City_Pop)
% add_city(Mpid, City_Name, City_Pop)
% add_city_return_id(Mpid, City_Name, City_Pop)
% delete_city_by_id(Mpid, City_Id)

%% with pool
% get_all_cities()
% update_city_by_id(City_Id, City_Name, City_Pop)
% add_city(City_Name, City_Pop)
% add_city_return_id(City_Name, City_Pop)
% delete_city_by_id(City_Id)

%% with pool and in transaction
% get_all_cities_t(Worker)
% add_city_t(Worker, City_Name, City_Pop)
% 


get_all_cities(Ppid) ->
  pgm:select(Ppid, "SELECT id, name, population FROM test ORDER BY id", []).


update_city_by_id(Ppid, City_Id, City_Name, City_Pop) ->
  pgm:in_up_del(Ppid, "UPDATE test SET name = $1, population = $2 WHERE id = $3", [City_Name, City_Pop, City_Id]).


add_city(Ppid, City_Name, City_Pop) ->
  pgm:in_up_del(Ppid, "INSERT INTO test (name, population) VALUES ($1, $2)", [City_Name, City_Pop]).


add_city_return_id(Ppid, City_Name, City_Pop) ->
  pgm:returning(Ppid, "INSERT INTO test (name, population) VALUES ($1, $2) RETURNING id", [City_Name, City_Pop]).


delete_city_by_id(Ppid, City_Id) ->
  pgm:in_up_del(Ppid, "DELETE FROM test WHERE id = $1", [City_Id]).



get_all_cities() ->
  pgm:select("SELECT id, name, population FROM test ORDER BY id", []).


update_city_by_id(City_Id, City_Name, City_Pop) ->
  pgm:in_up_del("UPDATE test SET name = $1, population = $2 WHERE id = $3", [City_Name, City_Pop, City_Id]).


add_city(City_Name, City_Pop) ->
  pgm:in_up_del("INSERT INTO test (name, population) VALUES ($1, $2)", [City_Name, City_Pop]).


add_city_return_id(City_Name, City_Pop) ->
  pgm:returning("INSERT INTO test (name, population) VALUES ($1, $2) RETURNING id", [City_Name, City_Pop]).


delete_city_by_id(City_Id) ->
  pgm:in_up_del("DELETE FROM test WHERE id = $1", [City_Id]).



get_all_cities_t(Worker) ->
  pgm:transaction_q(Worker, "SELECT id, name, population FROM test ORDER BY id", []).


add_city_t(Worker, City_Name, City_Pop) ->
  pgm:transaction_q(Worker, "INSERT INTO test (name, population) VALUES ($1, $2)", [City_Name, City_Pop]).