How can I check if a column exists in a trigger function?

Question

I want to create a trigger function. But I want to check if a specified column exists.

CREATE FUNCTION MyFunction()
RETURNS trigger AS '
BEGIN
  IF NEW.A >= 5 AND NEW.B <= 5 THEN
    // Do something ...
  END IF;
  RETURN NEW;
END' LANGUAGE 'plpgsql'

But I want to check if the column NEW.A exists. How can I do this?

IF EXISTS and use the columns view perhaps? See: https://www.postgresql.org/docs/9.5/static/infoschema-columns.html and here: https://dba.stackexchange.com/questions/22362/how-do-i-list-all-columns-for-a-specified-table — Jacob H, Jan 16 '18 at 14:09

Erwin Brandstetter · Accepted Answer · 2021-09-21T01:01:44.520

8

After converting the row to jsonb (not json in this case) use the ? operator to check for existence of a given key. But check for existence, before you run other checks that otherwise trigger exceptions.

CREATE OR REPLACE FUNCTION myfunction()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF to_jsonb(NEW) ? 'a' THEN  -- lower-case!
      IF NEW.a >= 5 AND NEW.b <= 5 THEN
         -- do something
      END IF;
   ELSE
      -- RAISE EXCEPTION ?
   END IF;
   RETURN NEW;
END
$func$;

The manual about the ? operator:

Does the text string exist as a top-level key or array element within the JSON value?

Asides:

Don't quote the language name, it's an identifier.
Careful with upper-case spellings. Your column names A ends up being a without double quotes ("A"). Better only use legal, lower-case identifiers to avoid confusion to begin with. See:
Are PostgreSQL column names case-sensitive?

edited Sep 21 '21 at 01:01

answered Jan 16 '18 at 16:14

Erwin Brandstetter

539,169
125
977
1,137

Does it matter for `jsonb` if my column name contains "_" like this `prop_name` – barteloma Jan 17 '18 at 06:07
@barteloma: No. Key names have to be quoted anyway. – Erwin Brandstetter Jan 17 '18 at 12:03
How do you "negate" the question mark operator to check if the NEW object does NOT contain a string? – s.k Sep 20 '21 at 21:08
@s.k: First of all, `?` checks whether a text string exists as a top-level key or array element. "contains a string" would be a much broader definition. Simple negation with `NOT NEW ? 'a'`. Careful, though! If there can be NULL values, you'll have to define how to handle those and adapt accordingly. Ask a new question with your details if you are unsure. You can always link to this one for context. – Erwin Brandstetter Sep 21 '21 at 00:59

score 4 · Answer 2 · answered Jan 16 '18 at 14:33

4

You can use json functions, e.g.:

if 'col' = any(select jsonb_object_keys(to_jsonb(new))) then
    raise notice 'column col exists';
...

answered Jan 16 '18 at 14:33

klin

99,138
12
177
203

Can I use `lower` function like this: `if lower('col') = any(...` – barteloma Jan 17 '18 at 06:28
1

Yes. You can also use `lower()` on the other side: `if lower('col') = any(select lower(jsonb_object_keys(to_jsonb(new)))) ...`. However, use the operator `?` like in the Erwin's answer - this should be more efficient. – klin Jan 17 '18 at 06:46

score 1 · Answer 3 · answered Jan 16 '18 at 14:17

1

Try it and handle exceptions:

BEGIN
   IF NEW.a >= 5 ...
EXCEPTION
   WHEN undefined_column THEN
      [column "a" does not exist]
END;

answered Jan 16 '18 at 14:17

Laurenz Albe

167,868
16
137
184

How can I check if a column exists in a trigger function?

3 Answers3