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?

Answer 1

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?

Answer 2

You can use json functions, e.g.:

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

Answer 3

Try it and handle exceptions:

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