Subject: Re: Error creating materialized view

From: David Mullineux <[email-masked]>
Date: Mon, 25 Nov 2024 08:58:15 +0000

On Mon, 25 Nov 2024, 06:08 Shaun Robinson, <[email-masked]>
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

---

From: Shaun Robinson <[email-masked]>
Date: Mon, 25 Nov 2024 09:36:09 +0000

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;