V
V
Vaskey2021-03-24 22:52:04
PostgreSQL
Vaskey, 2021-03-24 22:52:04

How to create an iterate function in postgres?

Good afternoon! I have two tables: records and fields . The records table has id and fields fields - for example, filled in as:
1) id = 2 (int), fields = 1,2,3 (string),
2) id = 3 (int), fields = 4,2,1 ( string)

and the fields table is filled:
1) id = 1, name=Pressure
2) id = 2, name=Pulse
3) id = 3, name=Pain 4) id = 4, name =
Joy

from tables get something like
id(records)=2 fields=Pressure, Pulse, Pain
id(recors)=3, fields=Joy, Pulse, Pressure

i.e. giving the row records, iterate over the values ​​of its fields field and insert the values ​​from the fields table into it

Thanks in advance for your help

Answer the question

In order to leave comments, you need to log in

1 answer(s)
S
Slava Rozhnev, 2021-03-24
@Vaskey

Don't thank:

SELECT records.id, array_agg(name) AS names
FROM records 
LEFT JOIN fields ON fields.id = ANY(records.fields)
GROUP BY records.id;

PostgreSQL fiddle
Anyway, it's a bad architecture to store a list of id's as a string

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question