X-Received: by 10.55.196.11 with SMTP id d11mr3233771qki.52.1525122185439; Mon, 30 Apr 2018 14:03:05 -0700 (PDT) X-Received: by 2002:a1f:aa8d:: with SMTP id t135-v6mr2044192vke.2.1525122185038; Mon, 30 Apr 2018 14:03:05 -0700 (PDT) Path: csiph.com!weretis.net!feeder6.news.weretis.net!feeder.usenetexpress.com!feeder-in1.iad1.usenetexpress.com!peer01.iad!feed-me.highwinds-media.com!news.highwinds-media.com!x25-v6no7555164qto.0!news-out.google.com!p41-v6ni757qtp.1!nntp.google.com!x25-v6no7555160qto.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.postgresql Date: Mon, 30 Apr 2018 14:03:04 -0700 (PDT) Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=80.116.47.113; posting-account=my2StAoAAACaEX7p5FmvhKoqKHWrMSFF NNTP-Posting-Host: 80.116.47.113 User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <4c19c4a8-7b74-4890-a349-4fc1c46f91c4@googlegroups.com> Subject: lateral query with dynamic column value From: luca.bocchi8@gmail.com Injection-Date: Mon, 30 Apr 2018 21:03:05 +0000 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable X-Received-Bytes: 7189 X-Received-Body-CRC: 3186567896 Xref: csiph.com comp.databases.postgresql:824 I need an help with lateral subqueries:=20 is it possible to perform it with a dynamically generated column name?=20 I'm trying it but i'm doing it wrong: -- table containing column names as values nov=3D# select rsi.display_field from corp_resumesectionitem rsi limit = 15; display_field =20 ----------------------------- 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) =20 -- import.vw_rpt_resume table contains several colum with names corresp= onding to the display_field values, such as 'resume_attribute_00064_13', 'r= esume_attribute_00065_13': =20 nov=3D# select column_name from information_schema.columns where table_= name=3D'vw_rpt_resume'; = = column_name =20 ------------------------------------ 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) =20 I need to perform a query with dynamic column names inside the LATERAL subq= uery, bu I'm messing something up... something like (this is more like pseudocode but it's just to give an idea)= : =20 select * from ( select rsi.display_field as df, subq.* from corp_resumesectionitem rsi, lateral ( execute 'select user_id, unnest(string_to_array(' || rsi.display_fi= eld '' ', ',')) as val, ' || rsi.display_field || ' as col_name from import= .vw_rpt_resume subq' ) =20 -- 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 =3D m.display_field inner join import.vw_rpt_resume_section_attribute_value_local as l on r.val =3D 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=20 ---------+-----+-----------------------------------------+-------------= --+-----------------+---------------------------+------------ 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 =20 ---------------------------+---------+---------------------------+-----= ---------------------- resume_attribute_00064_13 | 525 | resume_attribute_00064_13 | resum= e_attribute_00064_13 resume_attribute_00064_13 | 0 | resume_attribute_00064_13 | resum= e_attribute_00064_13 resume_attribute_00064_13 | 542 | resume_attribute_00064_13 | resum= e_attribute_00064_13 resume_attribute_00064_13 | 326 | resume_attribute_00064_13 | resum= e_attribute_00064_13 resume_attribute_00064_13 | 564 | resume_attribute_00064_13 | resum= e_attribute_00064_13 resume_attribute_00064_13 | 86 | resume_attribute_00064_13 | resum= e_attribute_00064_13 resume_attribute_00064_13 | 162 | resume_attribute_00064_13 | resum= e_attribute_00064_13 resume_attribute_00064_13 | 603 | resume_attribute_00064_13 | resum= e_attribute_00064_13 resume_attribute_00064_13 | 803 | resume_attribute_00064_13 | resum= e_attribute_00064_13 resume_attribute_00064_13 | 246 | resume_attribute_00064_13 | resum= e_attribute_00064_13 Any help about this?