This code correctly gathers diagnosis records for a patient to show distinct records based on the AMA ICD9 code, diagnosis description, and date of posting. The output is distinct on (ICD code, `cdesc`, `tposted`). The most recent date of usage, `tposted`, is assigned to each unique combination of (ICD9 code, `cdesc`). Additionally, each record has flags as to whether it is a chronic condition, chronic, and/or part of the past medical history, PMH. A problem/diagnosis is considered current/active, if it has reoccurred since its last resolution, has never been resolved, is chronic, or has occurred on the date of patient encounter, `tencounter`. The code also includes the fact that people often have more then one name associated with their account and that the tables are at least 2NF with "recid" as foreign key reference to other tables. My concerns are as follows: - The CTE's defined before the `LOOP` statement all apply to each of the select statements of the union (good). However, within the `LOOP`...`END` `LOOP` section, I am having to rewrite the "person" and "alldx" CTE for each associated `SELECT` statement. Is there any way to define these CTE's once, ideally in the first section and have enough permanence to be used in the `LOOP` section and avoid defining the same CTE multiple times? - Would it be faster to create multiple views and then perhaps do left `join`s or Updates to set the flags on each record as opposed to doing a loop on the output records? - Is there a better way to do this? How could this code be improved for speed? ``` CREATE OR REPLACE FUNCTION test (patient_recid integer, tencounter timestamp without time zone) RETURNS SETOF view_dx AS $BODY$ #variable_conflict use_column DECLARE r view_dx%rowtype; BEGIN -- will loop through all the records produced by the unions and assign tposted, pmh, chronic, etc... FOR r IN With person AS ( select p.chart_recid as recid from patients p where p.recid = patient_recid )

Here we trying to fix logs that have overlaps with each other. Am I doing it right? Is there any way to improve/refactor it? ``` DROP TYPE IF EXISTS logs_with_error; CREATE TYPE logs_with_error AS ( model_id INT, model_type TEXT, initiator_id INT, initiator_type TEXT, error_type_id INT, state INT ); CREATE OR REPLACE FUNCTION normalize_moderation_log_overflow() RETURNS INTEGER AS $BODY$ DECLARE logs logs_with_error%rowtype; model moderation_logs%rowtype; initiator moderation_logs%rowtype; BEGIN FOR logs IN SELECT model_id, model_type, initiator_id, initiator_type, error_type_id, state FROM errors WHERE state = #{ Error.states[:initial] } AND error_type_id = (SELECT id FROM error_types WHERE normalizer_type = 'moderation_log_overflow') LOOP SELECT * INTO model FROM moderation_logs WHERE id = logs.model_id; SELECT * INTO initiator FROM moderation_logs WHERE id = logs.initiator_id; IF model.curr_state = initiator.curr_state AND model.next_state = initiator.next_state THEN IF model.ended_at < initiator.ended_at THEN UPDATE moderation_logs SET ended_at = initiator.ended_at WHERE id = model.id; END IF; DELETE FROM moderation_logs WHERE id = initiator.id; ELSE UPDATE moderation_logs SET started_at = model.ended_at WHERE id = initiator.id; END IF; UPDATE errors SET state = #{ Error.states[:resolved] }; RAISE NOTICE '%', logs; END LOOP; RETURN 1; END $BODY$ LANGUAGE 'plpgsql' ; ```

Is it possible to expand a field value into a comparison operator? Something like this: ``` create table math ( value1 int, value2 int, operator text ); insert into math values(1,2,'>='); select * from math where value1 operator value2; ``` PS: I know that it is possible to solve this use case by means of `case when`, but want to know if there is an alternative solution.

I'm trying to escape a '%' character inside a format() function in PostgreSQL. The function replaces columns values based on a list of columns, deleting returns (\n) and trimming the strings. ``` CREATE OR REPLACE FUNCTION eliminar_retornos(text) RETURNS VOID AS $$ declare i text; fields text[] := ARRAY['direccion', 'localidad', 'calle', 'esq1', 'esq2', 'obs']; BEGIN FOREACH i IN ARRAY fields loop EXECUTE format( 'update %1$s set %2$s = trim(upper(replace(%2$s, E''\n'', '' '')))', $1, i) ; END LOOP; RAISE NOTICE 'Se actualizó la capa %', $1; END ; $$ LANGUAGE plpgsql; ``` I want to change this function to only replace those strings that have returns, using something like; ``` 'update %1$s set %2$s = trim(upper(replace(%2$s, E''\n'', '' ''))) where %2$s LIKE E''%\n%''', $1, i) ``` The problem is that I don't know how to correctly escape the '%' for the 'LIKE' inside the format() function. Sorry for posting something not reproducible, my knowledge is limited to achieve that.

I've defined some PL/pgSQL functions which get invoked from the client (e.g. psql, but could be JDBC/Hibernate as well), which is connected using a non super user role. e.g. `SELECT chk_pwd('(some.user,example.com)', 'P@ssW0rd');` I've also set `log_statement = 'none'` in the postgresql.conf of my server. That stops logging of all SQL statements. However, the signatures and invocations of the PL/pgSQL functions are still being logged, leaking sensitive data into the log files. How do I prevent this? On further investigation, it looks like the RAISE LOG statement in my function is causing this additional information to be printed. So, although it is just a log message, it seems to be getting treated as an error. Please correct me if I'm mistaken, but from reading the manual, there does not seem to be a way to suppress these additional messages while only printing the message from the RAISE LOG statement alone. An option like log_log_verbosity would have been useful. ``` 2021-02-27 17:42:58.483 IST [13008] myrole@test_db CONTEXT: PL/pgSQL function chk_pwd(email_t,character varying) line 10 at RAISE 2021-02-27 17:42:58.483 IST [13008] myrole@test_db STATEMENT: SELECT chk_pwd('(some.user,example.com)', 'P@ssW0rd'); ``` Finally, as per postgresql.conf, the definition of log_min_error_statement considers log at a higher error level than error, which is quite unintutive.I ended up resolving the situation by using RAISE NOTICE instead of RAISE LOG. However, the verbosity of NOTICE is controlled by log_error_verbosity. So, it continues to show the CONTEXT, though thankfully not the STATEMENT, thus preventing the leakage of sensitive values from the function parameters. Strictly speaking, I would not expect log_error_verbosity to be applicable to NOTICE, since it is not an error. Verbosity for NOTICE should be controlled by log_notice_verbosity (or log_non_error_verbosity), but there is no such setting available.

After some research I found an example where I can find tables and its columns with NULL values. But the function also returns true when the column is empty. It gives me errors when I try to add an or statement. How can I modify the function so it returns also true when the column contains blank values? This is the function I found: ``` create function has_nulls(p_schema in text, p_table in text, p_column in text) returns boolean language plpgsql as $$ declare b boolean; begin execute 'select exists(select * from '|| p_table||' where '||p_column||' is null)' into b; return b; end;$$; ```

I have a trigger function in PostgreSQL 12 which executes something like this: ``` CREATE OR REPLACE FUNCTION "my_latlon_function"() RETURNS trigger AS $$ BEGIN latcolumn:= ( SELECT column_name FROM information_schema.columns WHERE table_schema = TG_TABLE_SCHEMA AND table_name = TG_TABLE_NAME AND column_name ~* '.*lat.*' ); loncolumn := ( SELECT column_name FROM information_schema.columns WHERE table_schema = TG_TABLE_SCHEMA AND table_name = TG_TABLE_NAME AND column_name ~* '.*lon.*' ); EXECUTE 'select $1.' || loncolumn USING NEW INTO lon; EXECUTE 'select $1.' || latcolumn USING NEW INTO lat; -- do much stuff RETURN NEW; END $$ LANGUAGE 'plpgsql'; ``` The problem is, the `lat` and `lon` columns have capital letters in their name, e.g. `myLatitude` and `myLongitude`. The trigger function is able to retrieve these names, that's no problem. The problem lies in the two `EXECUTE` statements where it seems that the column names become lower-cased as stated by this error (in the underlying QUERY when the trigger is fired): ``` ERROR: column "mylongitude" not found in data type gpspoints LINE 1: select $1.myLongitude ^ QUERY: select $1.myLongitude CONTEXT: PL/pgSQL function my_latlon_function() line 24 at EXECUTE ********** Error ********** ERROR: column "mylongitude" not found in data type gpspoints SQL state: 42703 Context: PL/pgSQL function my_latlon_function() line 24 at EXECUTE ``` I know that PostgreSQL does need to enclose column names which have capital letters in their name into double quotes. Hence, I have tried to set double quotes in the two EXECUTE statements such as this: ``` EXECUTE 'select $1.' || "loncolumn" USING NEW INTO lon; EXECUTE 'select $1.' || "latcolumn" USING NEW INTO lat; ``` But the error stays exactly the same. If possible, how can I handle CamelCase column names in a PostgreSQL trig

I have the following function for geocoding, it works fine without the `EXCEPTION WHEN OTHERS THEN` part. However, I want the loop continue in case if there is an exception. So I am trying to do this by using `EXCEPTION WHEN OTHERS THEN` ``` CREATE OR REPLACE FUNCTION mygetcounty2() RETURNS void LANGUAGE PLPGSQL AS $$ DECLARE idVariable uuid; begin FOR i IN 1..100 LOOP SELECT id into idVariable FROM address_table WHERE county IS NULL AND parse_address_error = false LIMIT 1; UPDATE address_table set county = (select namelsad from tiger_data.county_all where ST_Contains( the_geom, ST_GeomFromText( (SELECT ST_AsText( ST_SnapToGrid(g.geomout, 0.00001) ) As wktlonlat FROM geocode(address, 1 ) AS g), 4269 ) )) where id = idVariable; EXCEPTION WHEN OTHERS THEN UPDATE address_table set parse_address_error = true where id = idVariable; END LOOP; return; end; $$; ``` But I am getting the following message when trying to create the function ``` ERROR: syntax error at or near "EXCEPTION" LINE 15: EXCEPTION WHEN OTHERS THEN ^ SQL state: 42601 Character: 511 ``` Any idea what I am doing wrong? EDIT: I added a new `begin`, `end` block inside the loop. I no longer got an error when creating the function. ``` Create or replace function mygetcounty2() returns void language plpgsql as $$ DECLARE idVariable uuid; begin FOR i IN 1..100 LOOP begin SELECT id into idVariable FROM address_table WHERE county IS NULL AND parse_address_error = false LIMIT 1; UPDATE address_table set county = (select namelsad from tiger_data.county_all where ST_Contains( the_geom, ST_GeomFromText( (SELECT ST_AsText( ST_SnapToGrid(g.geomout, 0.00001) ) As wktlonlat FROM geocode(address, 1 ) AS g), 4269 ) )) where id = idVariable; EXCEPTION WHEN OTHERS THEN raise notice 'oops %', sqlstate; UPDATE address_table set parse_address_error = true where id = idVariable; end; END LOOP; return; end; $$; ```

As I gain more experience with PostgreSQL I start to question the existence of PLPython. It's considered an "untrusted" language https://www.postgresql.org/docs/10/plpython.html What I am wondering is, when or why would anyone need to use this? PLPGSQL is already quite a strong language that allows you to do a lot of things. Has anyone here had the need to use it, and if so, for what?

Is it not possible to do a full row comparison in an INSERT/UPDATE trigger when the table has json or jsonb columns? The error I'm getting makes me think I'd need to individually compare each column because the `NEW.*/OLD.*` format can't be used to compare json columns. ``` CREATE OR REPLACE FUNCTION on_insert_update_modified() RETURNS TRIGGER AS $$ BEGIN IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN NEW.modified_at = now(); RETURN NEW; ELSE RETURN OLD; END IF; END; $$ language 'plpgsql'; CREATE TRIGGER mytable_insert_update BEFORE INSERT OR UPDATE ON mytable FOR EACH ROW EXECUTE PROCEDURE on_insert_update_modified(); ``` When I insert into the table which contains json and jsonb columns, I get the following error: `ERROR: operator does not exist: json = json LINE 1: SELECT row(NEW.*) IS DISTINCT FROM row(OLD.*) ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. QUERY: SELECT row(NEW.*) IS DISTINCT FROM row(OLD.*) CONTEXT: PL/pgSQL function on_insert_update_modified() line 3 at IF ` Is there a better way out of this?