Context: I have a table of disease and symptoms which was downloaded online. Each row represents a disease and has a variable number of populated columns which contain a string with both the symptom and the symptom frequency.
Target: I want to create a table which has a single column and lists all the symptoms.
How: To do this, I want to insert into the table the results from a query which takes the column name as a dynamic parameter, and loop over all the columns.
Problem: The problem is, the way I've written the loop, it sees the column name as a string instead of "evaluating" it as a column name.
What happens: Below is the code we have tried. The last part (5) is where the problem lies. The loop essentially does the following for each column name
SELECT left('C3', strpos('C3', ':') - 1) as symptom_desc
FROM base_table b
WHERE 'C3' !=''
instead of doing
SELECT left(C3, strpos(C3, ':') - 1) as symptom_desc
FROM base_table b
WHERE C3 !=''
/*************************************************************************************************************
* 1. Here we create a base table which is a subset of the raw data we have downloaded online
* and have imported in the database in its original format
*
* Each row represents a disease and has its associated symptoms and frequency across the columns to the right
*
* We will want to run a query on each column of this table through a loop to create a single column table with
* all the symptoms
*
**************************************************************************************************************8*/
DROP TABLE base_table;
CREATE TABLE base_table
(
C1 Varchar ,
C2 Varchar ,
C3 Varchar ,
C4 Varchar ,
C5 Varchar ,
C6 Varchar );
INSERT INTO base_table
VALUES
('Abdominal aortic aneurysm', 'Disease', 'anyloc:0.5', 'shock - hypovolemic:0.5' ,'flank pain:0.5', 'peripheral artery disease:0.5'),
('Abdominal aortic aneurysm ruptured', 'Disease', 'diabetes mellitus - type 2:0.5', 'anyloc:0.5', 'shock - hypovolemic:0.5', 'flank pain:0.5'),
('Abdominal pregnancy', 'Disease', 'increasing abdominal girth:0.5', 'anyloc:0.5', 'adult:0.5', 'amenorrhea:0.5'),
('Abo incompatibility reaction', 'Disease', 'fever:0.75', 'tachycardia:0.75', 'chills:0.5', 'rigors:0.5'),
('Abrasion of teeth', 'Disease', 'tongue jewellery:0.3', 'bruxism:0.6', 'high-fiber diet:0.1', 'loss of tooth structure:1'),
('Abscess of anal and rectal regions', 'Disease', 'severe anal pain:0.8', 'fever:0.75', 'malaise:0.75', 'draining fistula:0.5')
;
/***************************************************************************************
* 2. Here we inspect the format of the base table
***************************************************************************************/
SELECT *
FROM base_table;
/***************************************************************************************
* 3. Here we create a table shell to store only the symptom names
***************************************************************************************/
CREATE TABLE all_symptoms (symptom_desc varchar);
/***************************************************************************************
* 4. Here we demonstrate the query we wish to run on each column
***************************************************************************************/
--INSERT INTO all_symptoms
SELECT left(C3, strpos(C3, ':') - 1) as symptom_desc
FROM base_table b
WHERE C3 != ''
/***************************************************************************************
* 5. Here we try to use a FOR LOOP to run the above query for multiple columns
***************************************************************************************/
do
$$
declare
cn record;
begin
--The below creates a record for all the column names of the base table, except for the two first columns
for cn in SELECT column_name FROM information_schema.Columns WHERE table_schema = 'public' AND table_name = 'base_table' and column_name not in ('c1','c2')
--Here we attempt to loop through each of those columns, passing the column name as an argument
loop
INSERT all_symptoms
SELECT left(cn.column_name, strpos(cn.column_name, ':') - 1) as symptom_desc
FROM base_table b
WHERE cn.column_name != ''
;
end loop;
end;
$$
/*******************************************************************************************
*
* The problem is that is doesn't "evaluate" the column name but sees it as a string,
* i.e. it does
* SELECT left('C3', strpos('C3', ':') - 1) as symptom_desc
* FROM base_table b
* WHERE 'C3' !=''
*
* instead of doing
*
* SELECT left(C3, strpos(C3, ':') - 1) as symptom_desc
* FROM base_table b
* WHERE C3 !=''
*
******************************************************************************************** */
