Re: Error creating materialized view

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 <dmullx(at)gmail(dot)com> wrote:

>
>
> On Mon, 25 Nov 2024, 06:08 Shaun Robinson, <srobinson(at)mdxperience(dot)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
>>
>