Re: Error creating materialized view - Mailing list pgsql-general

Hi David,create table diagnosisTest
(
id serial primary key,
icd_code varchar(10)
);

create table encounterTest
(
id serial primary key,
dx1 integer,
dx2 integer,
dx3 integer,
dx4 integer,
dx5 integer,
dx6 integer,
dx7 integer,
dx8 integer,
dx9 integer,
dx10 integer,
dx11 integer,
dx12 integer
);

create table chargeTest
(
id serial primary key,
encounter_id integer,
amount varchar(10),
dx_list text
);

INSERT INTO diagnosisTest (icd_code)
VALUES ('M2.1');
INSERT INTO diagnosisTest (icd_code)
VALUES ('M2.2');
INSERT INTO diagnosisTest (icd_code)
VALUES ('M2.3');
INSERT INTO diagnosisTest (icd_code)
VALUES ('M2.4');
INSERT INTO diagnosisTest (icd_code)
VALUES ('M2.5');
INSERT INTO diagnosisTest (icd_code)
VALUES ('M2.6');
INSERT INTO diagnosisTest (icd_code)
VALUES ('M2.7');
INSERT INTO diagnosisTest (icd_code)
VALUES ('M3.3');
INSERT INTO diagnosisTest (icd_code)
VALUES ('M4.4');
INSERT INTO diagnosisTest (icd_code)
VALUES ('M6.5');


insert into encounterTest(dx1, dx2, dx3)
VALUES (1, 4, 6);
insert into encounterTest(dx1, dx2, dx3)
VALUES (7, 1, 9);
insert into encounterTest(dx1, dx2, dx3)
VALUES (10, 3, 1);
insert into encounterTest(dx1, dx2, dx3)
VALUES (5, 4, 1);

insert into chargeTest (encounter_id, amount, dx_list)
VALUES (1, '100.00', '1, 2');
insert into chargeTest (encounter_id, amount, dx_list)
VALUES (2, '500.00', '1,2,3');
insert into chargeTest (encounter_id, amount, dx_list)
VALUES (3, '300.00', '1,2,3');


CREATE OR REPLACE FUNCTION get_chg_dxs_test(INTEGER)
RETURNS TABLE
(
dx_codes TEXT,
primary_dx TEXT
)
AS
$$
DECLARE
chg_id ALIAS FOR $1;
chg_row chargeTest%ROWTYPE;
enc_row encounterTest%ROWTYPE;
chg_dxs TEXT[];
dx_list TEXT[];
loop_counter INTEGER;
current_dx_str TEXT;
primary_dx TEXT;
BEGIN
SELECT * INTO chg_row FROM chargeTest c WHERE c.id = chg_id;
SELECT * INTO enc_row FROM encounterTest e WHERE e.id = chg_row.encounter_id;
SELECT regexp_split_to_array(coalesce(chg_row.dx_list, ''), ',') INTO chg_dxs;

loop_counter = 0;

LOOP
EXIT WHEN loop_counter = (array_length(chg_dxs, 1));
loop_counter := loop_counter + 1;

IF chg_dxs[loop_counter] = '1'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id = enc_row.dx1;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '2'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id = enc_row.dx2;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '3'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id = enc_row.dx3;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '4'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id = enc_row.dx4;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '5'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id = enc_row.dx5;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '6'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id = enc_row.dx6;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '7'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id = enc_row.dx7;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '8'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id = enc_row.dx8;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '9'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id = enc_row.dx9;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '10'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id = enc_row.dx10;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '11'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id = enc_row.dx11;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF chg_dxs[loop_counter] = '12'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id = enc_row.dx12;
dx_list := array_append(dx_list, current_dx_str);
END IF;

IF loop_counter = 1
THEN
primary_dx := current_dx_str;
END IF;

END LOOP;

RETURN QUERY SELECT array_to_string(dx_list, ','),
primary_dx;
END;
$$ LANGUAGE plpgsql;

create materialized view vtest as
SELECT chg.id AS charge_id,

(get_chg_dxs_test(chg.id)).primary_dx AS primary_dx
FROM chargeTest chg
with data;