Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tFVW8-00DQt2-Bq for pgsql-general@arkaria.postgresql.org; Mon, 25 Nov 2024 09:36:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tFVW6-00DqCJ-1o for pgsql-general@arkaria.postgresql.org; Mon, 25 Nov 2024 09:36:26 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tFVW5-00DqCB-D3 for pgsql-general@lists.postgresql.org; Mon, 25 Nov 2024 09:36:25 +0000 Received: from mail-pl1-x62b.google.com ([2607:f8b0:4864:20::62b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tFVW2-003kSK-OM for pgsql-general@lists.postgresql.org; Mon, 25 Nov 2024 09:36:24 +0000 Received: by mail-pl1-x62b.google.com with SMTP id d9443c01a7336-212008b0d6eso33928755ad.3 for ; Mon, 25 Nov 2024 01:36:22 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=mdxperience.com; s=google; t=1732527380; x=1733132180; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=2mpVNAmHVRG3Fl2xjjMiXCiBuCWxb1ZFWlK2ie2Y3ts=; b=pJanVrK+fsXcCSwIeOUc8rgBNQHoZ1wj5W0pQkZEDtCTtD5f8A0//5OFiJhj3z1YAS EARuHPeVAyv0Rvzxt4QsfSa/CyZzxAS15JxbVo3xn6bvKCzVJ7PFilEsaLxqiPJcUzix NuN8iADLYGZ3HeO2B0//Et+eyuMhN7SlxjACIRaPO+LP9e1MKEpkPbdqaV0IhgxoLu20 v13I8SizoZCQ8kdylNyjU+Tm6LyN7xsgiwnAJqPiUaPH0U6PYg/N0/NFOf9oy/gkxacd N+yBD9OsWZAiPiy+YVHGAVbPL5DKH4JmW47V5iU5+PILc34lpLz/rVnUo0wTipP3Zqwo jCpw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1732527380; x=1733132180; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=2mpVNAmHVRG3Fl2xjjMiXCiBuCWxb1ZFWlK2ie2Y3ts=; b=lYFb25w9g+R8yx/1nEuZjULG5rlazHKPbFJrxAP+9L7oHMV/pRKFCzBz0M2Uoy2x61 rv8F4R2cs0LUP5CCujM+3fGD7QF601kqi+Ljee6VanUdQRPAIS3oWVIVnGlU8tbX8l4X b/a8KRhveR805LVMDyVRE/2dQfjQNVmQPCfjPGqLG/7/D4VYK5/mt5xbuQR7AvXIY1Bq NvS2TH0vkMJQC2rbvzwHZ2gSjq1qGn9Ll25b8gt8wdqheuXhXoGZ2HOyqHeiYud00svN vlGXXh/0g8YRVF4iKFrIpidz1ui+0gITGaTBESik4WwCusxPEXqO+lKHiG69pGKFrVa/ 8+3g== X-Gm-Message-State: AOJu0YwqgKGYHykVxQfipP86fBJTiRfgQGd26Jx6WN409ZUPrMNVoNz4 C7LAm5fK5bSVbVXzwiLIvZKrVNjmVe0OTbi5xBQjNFMYw43ytvYDINMGSImi7zVlgA9MLn4W2QB xNmw3q4NfQvwvxnPYRLRYWr1Ac3w8GApOdL3ydg== X-Gm-Gg: ASbGncvwEwVAcs8MOJNAlFYj6WFaepPdbkprctRMU6hPuRmib+A9ArnhuSikGzp+BGL ZoT0EBjLvOnsU3iCjGFrJuziZDSzl X-Google-Smtp-Source: AGHT+IH3pdQccCZiOkStQBeuRS7Xvr1ErC2zeyM/BRJ+cuFaTYTCCLeRSyQV8gjowkWydzNdTYIcYR5NgVLGMLULgI4= X-Received: by 2002:a17:902:db0f:b0:20c:b485:eda3 with SMTP id d9443c01a7336-2129f2262a6mr156032095ad.20.1732527380409; Mon, 25 Nov 2024 01:36:20 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Shaun Robinson Date: Mon, 25 Nov 2024 09:36:09 +0000 Message-ID: Subject: Re: Error creating materialized view To: David Mullineux Cc: pgsql-general Content-Type: multipart/alternative; boundary="00000000000012cf9a0627b97790" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000012cf9a0627b97790 Content-Type: text/plain; charset="UTF-8" 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 wrote: > > > On Mon, 25 Nov 2024, 06:08 Shaun Robinson, > 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 >> > --00000000000012cf9a0627b97790 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi David,

I've created a basic exam= ple which produces the issue for me and the SQL is below.

Thanks
Shaun


create ta= ble 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 inte= ger,
dx11 integer,
dx12 integer
);

create table chargeTest<= br>(
id serial primary key,
encounter_id integer,
amount varchar(1= 0),
dx_list text
);

INSERT INTO diagnosisTest (icd_code)
VA= LUES ('M2.1');
INSERT INTO diagnosisTest (icd_code)
VALUES (&= #39;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');
IN= SERT INTO diagnosisTest (icd_code)
VALUES ('M2.6');
INSERT IN= TO diagnosisTest (icd_code)
VALUES ('M2.7');
INSERT INTO diag= nosisTest (icd_code)
VALUES ('M3.3');
INSERT INTO diagnosisTe= st (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)
VALUE= S (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 (encounte= r_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_ch= g_dxs_test(INTEGER)
RETURNS TABLE
(
dx_codes TEXT,
primary_dx T= EXT
)
AS
$$
DECLARE
chg_id ALIAS FOR $1;
chg_row chargeTe= st%ROWTYPE;
enc_row encounterTest%ROWTYPE;
chg_dxs TEXT[];
dx_list= TEXT[];
loop_counter INTEGER;
current_dx_str TEXT;
primary_dx TEX= T;
BEGIN
SELECT * INTO chg_row FROM chargeTest c WHERE c.id =3D chg_id;
SELECT * INTO enc_row FROM encounterTest = e WHERE e.id =3D chg_row.encounter_id;
SELEC= T regexp_split_to_array(coalesce(chg_row.dx_list, ''), ',')= INTO chg_dxs;

loop_counter =3D 0;

LOOP
EXIT WHEN loop_cou= nter =3D (array_length(chg_dxs, 1));
loop_counter :=3D loop_counter + 1;=

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

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

IF chg_dxs[loop_counter] =3D &= #39;3'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTes= t WHERE id =3D enc_row.dx3;
dx_list :=3D array_append(dx_list, current_d= x_str);
END IF;

IF chg_dxs[loop_counter] =3D '4'
THEN<= br>SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id =3D enc_= row.dx4;
dx_list :=3D array_append(dx_list, current_dx_str);
END IF;<= br>
IF chg_dxs[loop_counter] =3D '5'
THEN
SELECT icd_code = INTO current_dx_str FROM diagnosisTest WHERE id =3D enc_row.dx5;
dx_list= :=3D array_append(dx_list, current_dx_str);
END IF;

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

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

IF chg_dxs[loop_counter] =3D '8'
THENSELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id =3D enc_r= ow.dx8;
dx_list :=3D array_append(dx_list, current_dx_str);
END IF;
IF chg_dxs[loop_counter] =3D '9'
THEN
SELECT icd_code I= NTO current_dx_str FROM diagnosisTest WHERE id =3D enc_row.dx9;
dx_list = :=3D array_append(dx_list, current_dx_str);
END IF;

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

IF chg_dxs[loop_counter] =3D &= #39;11'
THEN
SELECT icd_code INTO current_dx_str FROM diagnosisTe= st WHERE id =3D enc_row.dx11;
dx_list :=3D array_append(dx_list, current= _dx_str);
END IF;

IF chg_dxs[loop_counter] =3D '12'
TH= EN
SELECT icd_code INTO current_dx_str FROM diagnosisTest WHERE id =3D e= nc_row.dx12;
dx_list :=3D array_append(dx_list, current_dx_str);
END = IF;

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

END LOOP;

RETURN QUERY SELECT array_to_string(dx_= list, ','),
primary_dx;
END;
$$ LANGUAGE plpgsql;

c= reate materialized view vtest as
SELECT chg.id= AS charge_id,

(get_chg_dxs_test(chg.i= d)).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, 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=C2=A0materialized view which works in version 16 and below= . The sql works fine running as a query, but adding the create=C2=A0materia= lized view breaks it.

The error comes when calling= a custom function and the error is that a relation doesn't exist (whic= h it does as it works within the same query when not creating a view).

Is this a known issue in the version 17.2?
<= br>
Thanks
Shau


Can = you please post the DDL statements so we can see?
Th= anks
can you
--00000000000012cf9a0627b97790--