Hi David, I've created a basic example which produces the issue for me and the SQL is below.
Thanks Shaun 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; On Mon, 25 Nov 2024 at 08:58, David Mullineux <dmu...@gmail.com> wrote: > > > On Mon, 25 Nov 2024, 06:08 Shaun Robinson, <srobin...@mdxperience.com> > wrote: > >> Hi, >> >> I'm currently testing an application with Postgres 17.2 and am getting an >> error when creating a materialized view which works in version 16 and >> below. The sql works fine running as a query, but adding the >> create materialized view breaks it. >> >> The error comes when calling a custom function and the error is that a >> relation doesn't exist (which it does as it works within the same query >> when not creating a view). >> >> Is this a known issue in the version 17.2? >> >> Thanks >> Shau >> > > > Can you please post the DDL statements so we can see? > Thanks > >> can you >> >