Error creating materialized view

On Mon, 25 Nov 2024, 06:08 Shaun Robinson, <srobinson@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

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@gmail.com> wrote:

On Mon, 2024-11-25 at 06:08 +0000, Shaun Robinson wrote:

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?

That's a deliberate improvemen; see the first point in
https://www.postgresql.org/docs/current/release-17.html#RELEASE-17-MIGRATION

you will have to schema-qualify all tables in the function or set the
"search_path" on the function.

Yours,
Laurenz Albe