Converting string to expression in PL/PGSQL

Question

I am trying to convert a list of columns (retrieved from the information schema) into an expression recognizable by PostgreSQL. See the illustration below:

columns:= {'column1','column2','column3'};

I should be able to change the code above into:

columns2:= {column1,column2,column3};

I need to do this in order for me to unpivot a horizontal table as shown below:

CREATE TABLE tbl1 AS SELECT id, unnest(columns) AS key, unnest(columns2) AS value
FROM tbl_orig;

Right now, because the elements are strings, I just end up with values that are column names and not the value itself.

 id| key |value |
  1|col1 | col1 |

Instead of

  id | key    |    value    |
   1 |  col1  | val_of_col1 |

I need this because I am trying to achieve the code below but with dynamically-retrieved columns:

 CREATE OR REPLACE VIEW vw_zcta_unpivot_ary
 AS 
 SELECT zip
 , unnest(
  array['hu10', 'aland', 'pop10'
 , 'awater', 'intptlat', 'intptlong', 'aland_sqmi', 'awater_sqmi'] 
) AS key
, unnest(
array[hu10::text, aland::text, pop10::text
  , awater::text, intptlat::text, intptlong::text
  , aland_sqmi::text, awater_sqmi::text] 
) AS val
FROM zcta5;

The title indicates this is inside a pl/pgsql function - is that correct? Have you looked at dynamic SQL? — harmic, Apr 04 '14 at 09:02
I suspect you want `EXECUTE`, but really can't follow your question. — Craig Ringer, Apr 05 '14 at 05:55

score 0 · Answer 1 · edited May 23 '17 at 11:49

0

I accomplished my need to have dynamic columns by the code found at Loop through columns of RECORD.

I used it in the following way:

CREATE TABLE tbl2 AS SELECT study,unnest(columns) AS key, unnest_table('tbl_orig') AS value FROM tbl_orig;

edited May 23 '17 at 11:49

Community

1
1

answered Apr 05 '14 at 05:55

jackeblagare

407
7
21

Converting string to expression in PL/PGSQL

1 Answers1