Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #824

lateral query with dynamic column value

Show key headers only | View raw

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?

Back to comp.databases.postgresql | Previous | Next — Next in thread | Find similar

Thread

lateral query with dynamic column value luca.bocchi8@gmail.com - 2018-04-30 14:03 -0700
  Re: lateral query with dynamic column value Dimitri Fontaine <dim@tapoueh.org> - 2018-05-01 09:50 +0200

csiph-web