lateral query with dynamic column value

[luca.b...@gmail.com‏]

I need an help with lateral subqueries:

is it possible to perform it with a dynamically generated column name?

I'm trying it but i'm doing it wrong:

-- table containing column names as values
nov=# select rsi.display_field from corp_resumesectionitem rsi limit 15;
display_field
-----------------------------
resume_attribute_00064_13
resume_attribute_00065_13
resume_attribute_00066_13
resume_attribute_00067_18_1
resume_attribute_00067_18_2
resume_attribute_00068_1
resume_attribute_00069_1
resume_attribute_00070_13
resume_attribute_00071_13
resume_attribute_00072_13
resume_attribute_00082_1
resume_attribute_00083_1
resume_attribute_00084_6
resume_attribute_00085_13
resume_attribute_00086_3
(15 rows)

-- import.vw_rpt_resume table contains several colum with names corresponding to the display_field values, such as 'resume_attribute_00064_13', 'resume_attribute_00065_13':

nov=# select column_name from information_schema.columns where table_name='vw_rpt_resume'; column_name
------------------------------------
user_id
warehouse_resume_attribute_user_id
resume_attribute_000m1_2
resume_attribute_00032_13
resume_attribute_00052_13
resume_attribute_00053_13
resume_attribute_00057_4
resume_attribute_00058_7
resume_attribute_00059_6
resume_attribute_00061_3
...
...
resume_attribute_00094_18_1
resume_attribute_00094_18_2
resume_attribute_00095_1
resume_attribute_00096_13
resume_attribute_00097_13
resume_attribute_00098_7
resume_attribute_00099_2
resume_attribute_00100_13
resume_attribute_00101_13
resume_attribute_00102_13
(55 rows)


I need to perform a query with dynamic column names inside the LATERAL subquery, bu I'm messing something up...

something like (this is more like pseudocode but it's just to give an idea):

select * from (
select
rsi.display_field as df,
subq.*
from corp_resumesectionitem rsi, lateral (
execute 'select user_id, unnest(string_to_array(' || rsi.display_field '' ', ',')) as val, ' || rsi.display_field || ' as col_name from import.vw_rpt_resume subq'
)

-- but this is not working...


each lateral query should be something like:

select * from (
select distinct r.user_id, r.val, m.name_display, l.rsal_value_id, l.rsal_title, m.display_field, l.culture_id
from (
select user_id,
unnest(string_to_array(resume_attribute_00032_13, ',')) as val,
'resume_attribute_00032_13' as col_name from import.vw_rpt_resume
) as r
inner join import.custom_fields_mapping m
on r.col_name::text = m.display_field
inner join import.vw_rpt_resume_section_attribute_value_local as l
on r.val = l.rsal_value_id) as dd;


with as result somethig like this:

user_id | val | name_display | rsal_value_id | rsal_title | display_field | culture_id
---------+-----+-----------------------------------------+---------------+-----------------+---------------------------+------------
56 | 226 | Skills & Knowledge - Skills & Knowledge | 226 | Being resilient | resume_attribute_00032_13 | 1
56 | 226 | Skills & Knowledge - Skills & Knowledge | 226 | Being resilient | resume_attribute_00032_13 | 10
56 | 226 | Skills & Knowledge - Skills & Knowledge | 226 | Being resilient | resume_attribute_00032_13 | 11
56 | 226 | Skills & Knowledge - Skills & Knowledge | 226 | Being resilient | resume_attribute_00032_13 | 12
56 | 226 | Skills & Knowledge - Skills & Knowledge | 226 | Being resilient | resume_attribute_00032_13 | 13
56 | 226 | Skills & Knowledge - Skills & Knowledge | 226 | Being resilient | resume_attribute_00032_13 | 14
56 | 226 | Skills & Knowledge - Skills & Knowledge | 226 | Being resilient | resume_attribute_00032_13 | 15
56 | 226 | Skills & Knowledge - Skills & Knowledge | 226 | Being resilient | resume_attribute_00032_13 | 16
56 | 226 | Skills & Knowledge - Skills & Knowledge | 226 | Being resilient | resume_attribute_00032_13 | 17
56 | 226 | Skills & Knowledge - Skills & Knowledge | 226 | Being resilient | resume_attribute_00032_13 | 18


but instead I got this:

df | user_id | val | col_name
---------------------------+---------+---------------------------+---------------------------
resume_attribute_00064_13 | 525 | resume_attribute_00064_13 | resume_attribute_00064_13
resume_attribute_00064_13 | 0 | resume_attribute_00064_13 | resume_attribute_00064_13
resume_attribute_00064_13 | 542 | resume_attribute_00064_13 | resume_attribute_00064_13
resume_attribute_00064_13 | 326 | resume_attribute_00064_13 | resume_attribute_00064_13
resume_attribute_00064_13 | 564 | resume_attribute_00064_13 | resume_attribute_00064_13
resume_attribute_00064_13 | 86 | resume_attribute_00064_13 | resume_attribute_00064_13
resume_attribute_00064_13 | 162 | resume_attribute_00064_13 | resume_attribute_00064_13
resume_attribute_00064_13 | 603 | resume_attribute_00064_13 | resume_attribute_00064_13
resume_attribute_00064_13 | 803 | resume_attribute_00064_13 | resume_attribute_00064_13
resume_attribute_00064_13 | 246 | resume_attribute_00064_13 | resume_attribute_00064_13

Any help about this?

[Dimitri Fontaine‏]

luca.b...@gmail.com writes:
> I need an help with lateral subqueries:
>
> is it possible to perform it with a dynamically generated column name?

No it is not. SQL is a statically typed language, the SQL engine
(parser, executor) needs to fully determine the data type of the result
of the query before running it.

column_name
> ------------------------------------
> user_id
> warehouse_resume_attribute_user_id
> resume_attribute_000m1_2
> resume_attribute_00032_13
> resume_attribute_00052_13
> resume_attribute_00053_13
> resume_attribute_00057_4
> resume_attribute_00058_7
> resume_attribute_00059_6
> resume_attribute_00061_3

This looks like an EAV data model, which is the worst possible choice on
earth in the relational world. Normalize your data model and then it's
going to be very easy (and efficient) to write your queries.

Regards,
--
Dimitri Fontaine
https://masteringpostgresql.com