loop through arrays in postgresql table return based on condition and array element position

Question

I have the following row in a table in a posgresql db:

INSERT INTO "public"."position" ("id",
                                 "layout_id",
                                 "dining_table_id",
                                 "x_position",
                                 "y_position",
                                 "translate_x",
                                 "translate_y",
                                 "rotation",
                                 "start_timestamps",
                                 "end_timestamps")
VALUES (683, 32, 683, 1288, 0, E'{0,25}', E'{134,-98}', 0, E'{"2019-03-05 10:24:00","2019-04-05 10:24:00"}', E'{"2019-03-05 21:00:00","2019-04-05 21:00:00"}');

I want to make an query, which returns me:

x_position
y_position
rotation

and

translate_x
translate_y

But in those columns only if the following condition is met:

If a given timestamp (which comes from the front end and should be part of the condition of the query) is bigger or equal to the start_timestamps and smaller than the end_timestamps array element, which have the same position in the array as the translate_x and translate_y array elements.

So for example if the given timestamp is: 2019-03-05 12:00:00 the array element of the translate_x column with the value 0 (position 0) and the array element of the translate_y column with the value 134 (position 0) should be returned, because 2019-03-05 12:00:00 is smaller than end_timestamps columns array element (position 0) and is bigger or equal than the start_timestamps columns array element (position 0).

My question is how can I query the table accordingly? (I hope my table structure makes sense)

My try:

const result = await this.db.query(`
SELECT 
       p.x_position,
       p.y_position,
       p.rotation,
FROM POSITION p 
DECLARE 

s int8 := 0;
x int;

BEGIN
FOR x IN s..p.start_timestamps.length LOOP IF p.start_timestamps[x] <= $1
AND p.end_timestamps[x] > $1 THEN RETURN p.translate_x[x], p.translate_y[x] END LOOP;`
[timestamp]);

Please do not post table definitions as screen shots, edit your question and add the CREATE TABLE statement for the table in question as formatted text (the same way you did it with the INSERT statement) — user330315
– user330315, Commented May 9, 2019 at 9:18

Rémy Baron · Accepted Answer · 2019-05-09 10:18:59Z

2

If If I understood correctly, you can do something like this:

select * from (
    select i,id,layout_id,dining_table_id,x_position,y_position,translate_x[i],
           translate_y[i],start_timestamps[i],end_timestamps[i] from (
                          select generate_subscripts(translate_x,1) i,* from position
                  ) a
   ) b where start_timestamps<='2019-03-05 12:00:00'::timestamp 
            and end_timestamps>'2019-03-05 12:00:00'::timestamp

It should work, but if you can change the definition of your database, you should create a new table for example:

position_periods : (id_position integer ,start_timestamp timestamp,end_timestamp timestamp,translate_x integer,translate_y integer)

edited May 9, 2019 at 10:18

answered May 9, 2019 at 10:09

Rémy Baron

1,4099 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

loop through arrays in postgresql table return based on condition and array element position

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related