Using custom function and returning json val from Postgres

Phoenix Forum Questions / Help
, , ,
1

PROBLEM IDENTIFIED

select test('2021-09-10 00:00:00Z', '2022-09-11 00:00:00Z', string_to_array('2864,8964', ',')::int[]) FROM hit_count; stalls even when run directly, which is what ecto is attempts to run. How do I run a SELECT through Ecto without a FROM condition?

I want to use Ecto.Query to add additional scrubbing… though its already being done.

Im doing this so I can run stats on the “keys” passed to postgres (which is not shown in code). If I call the postgres function directly it works ie hit_db=# SELECT test('2021-09-10 00:00:00Z', '2022-09-11 00:00:00Z', string_to_array('2864,8964', ',')::int[]);

If I call it through ecto it times out. Any ideas?

Side note… Id rather not call the function with string_to_array(?, ',')::int[] but instead use VARIADIC key_list int[] in the Postgres function and accept something like test(from, to, 1234,2345,3456,...nth_key)… any ideas here?

list = Enum.join(key_list, ",")

    ApiHitCountTimeseriesRepo.all(
      from(d in "hit_count",
        select: %{
          test: fragment("test(?,?,string_to_array(?, ',')::int[])", ^from, ^to, ^list)
        }
      ) |> IO.inspect()
    ) |> IO.inspect()

Postgres function :

CREATE FUNCTION test(from timestamptz, to timestamptz, key_list int[]) RETURNS json 
AS 
'SELECT json_object_agg(foo.data_timestamp, foo.json) 
FROM (
SELECT data_timestamp, jsonb_object_agg(key, value) as json 
FROM hit_count 
CROSS JOIN jsonb_each(data) 
WHERE data_timestamp >= $1 
AND data_timestamp <= $2 
AND key::int = ANY ($3) 
GROUP BY data_timestamp
) as foo;' 
LANGUAGE SQL;

errors


Postgrex.Protocol (#PID<0.580.0>) disconnected: ** (DBConnection.ConnectionError) client #PID<0.1623.0> timed out because it queued and checked out the connection for longer than 15000ms

if I pass 1 key


** (exit) an exception was raised:
    ** (Postgrex.Error) ERROR 57014 (query_canceled) canceling statement due to user request
        (ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:932: Ecto.Adapters.SQL.raise_sql_call_error/1
        (ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:847: Ecto.Adapters.SQL.execute/6
        (ecto 3.8.4) lib/ecto/repo/queryable.ex:221: Ecto.Repo.Queryable.execute/4
        (ecto 3.8.4) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3

if I pass multiple keys

** (exit) an exception was raised:
    ** (DBConnection.ConnectionError) tcp recv: closed (the connection was closed by the pool, possibly due to a timeout or because the pool has been terminated)
        (ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:932: Ecto.Adapters.SQL.raise_sql_call_error/1
        (ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:847: Ecto.Adapters.SQL.execute/6
        (ecto 3.8.4) lib/ecto/repo/queryable.ex:221: Ecto.Repo.Queryable.execute/4
        (ecto 3.8.4) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
2

This can be done but its hacky. However, IMO it should not be removed (PLEASE DONT REMOVE THIS we want the query scrubbed without the FROM condition) taking from SQL injection you can use -- to rem out the FROM condition.

MyRepo.all(
      from(d in "_",
        select: %{
          test: fragment("test(?,?,string_to_array(?, ',')::int[])--", ^from, ^to, ^list)
        }
      )
    )