Checking every column in inserted record

Question

I am trying to write a generic plpgsql function that won't allow user to insert record with empty strings to a table.

My problem is that if NEW.nazwa_kolumny looks for column named "nazw_kolumny" rather than substitute the variable's value. After some research I wrote the code below, but apparently NEW is not known in that EXECUTE statement.

CREATE OR REPLACE FUNCTION czy_pusty_rekord() RETURNS TRIGGER
AS $$
DECLARE
  nazwy_kolumn TEXT[];
  nazwa_kolumny TEXT;
  czy_pusty BOOLEAN;
BEGIN
  nazwy_kolumn := array(SELECT column_name::text
                        FROM information_schema.columns
                        WHERE table_name = TG_TABLE_NAME);
  FOREACH nazwa_kolumny IN ARRAY nazwy_kolumn
  LOOP
    EXECUTE 'EXISTS SELECT * FROM NEW WHERE ' || quote_ident(nazwa_kolumny) || ' = ''''' INTO czy_pusty;
    IF (czy_pusty) THEN
      RAISE EXCEPTION 'Nie podano wszystkich wymaganych danych';
    END IF;
  END LOOP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Have a look at [Loop through columns of RECORD](https://stackoverflow.com/q/13065774/1048572). Today it might be easier with `json_each(to_json(NEW))` though — Bergi, Dec 18 '22 at 17:25

Schwern · Answer 1 · 2022-12-18T02:42:32.020

trying to write a generic plpgsql functin that won't allow user to insert record with empty strings to a table

This should instead be done with a check constraint on the columns in question. This is more flexible and more visible than a trigger on all columns.

create table example (
  id serial primary key,
  name text not null check(regexp_like(name, '[^[:space:]]'))
)

Demonstraton

You can avoid repeating the regexp by using a function.

create function not_blank(str text) returns boolean
  language sql
  immutable
  return regexp_like(str, '[^[:space:]]');

create table example (
  id serial primary key,
  name text not null check(not_blank(name))
)

Checking every column in inserted record

1 Answers1