column "{refnr,beruf,titel,..." not found in data type stellenangebote_active pg(plpgsql_check) #495
Description
Bug report
I switched to using the vscode extension that relies on this server from UniqueVision's unmaintained extension and despite initial hiccups, it is working right now and I am very grateful for it.
- I confirm this is a bug with Supabase, not with my own application.
- I confirm I have searched the Docs, GitHub Discussions, and Discord.
Describe the bug
Most of my extension code is interpreted correctly and good feedback is given by the server and the syntax checker except for one instance: (an auditing trigger function)
create or replace function stellenangebote_update_audit_handler ()
returns trigger language plpgsql as $audit$
declare
column_names text[] := array[
'refnr',
'beruf',
'titel',
'ort',
'plz',
'region',
'land',
'lat',
'lon',
'arbeitgeber',
'veroeffentlichungsdatum',
'modifikationstimestamp',
'eintrittsdatum',
'kundennummerhash',
'externeurl'
];
t text := null;
is_equal boolean;
begin
foreach t in array column_names loop
execute format('select ($1).%1$I is not distinct from ($2).%1$I', t)
using old, new into is_equal;
if not is_equal then
execute format('insert into stellenangebote_update_audit '
'(job_refnr, column_name, old_value, new_value) values '
'($1, ''%1$s'', ($2).%1$I::text, ($3).%1$I::text)', t)
using old.refnr, old, new;
end if;
end loop;
return new;
end $audit$;Note that the stellenangebote and the audit tables are defined as follows:
create table if not exists stellenangebote_update_audit (
created timestamp primary key default current_timestamp,
job_refnr text not null,
column_name text not null,
old_value text,
new_value text
);
create table if not exists stellenangebote (
refnr varchar(32) not null,
beruf text null,
titel text null,
ort text null,
plz ValidPLZ null,
region text null,
land text null,
lat numeric null,
lon numeric null,
arbeitgeber text null,
veroeffentlichungsdatum date null,
modifikationsTimestamp timestamp null,
eintrittsdatum date null,
kundennummerHash text null,
externeUrl text null,
created timestamp not null default current_timestamp,
updated timestamp not null default current_timestamp,
deleted timestamp null,
archive_partition smallint not null default 0 check (archive_partition >= 0)
) partition by range(archive_partition);To Reproduce
NA
Expected behavior
The code works great so there should not be an error. It seems as if the syntax checker things that the array of column names is a column name which is not the case. This may have also to do with the fact I am using a partitioned table, not entirely sure.
Screenshots
System information
- OS: Linux
Additional context
NA