10

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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
barteloma
  • 6,403
  • 14
  • 79
  • 173
  • 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
  • Do you mean `IF EXIST NEW.A THEN ....` do something. – barteloma Jan 16 '18 at 14:14

3 Answers3

12

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?
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 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? – swiss_knight 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
4

You can use json functions, e.g.:

if 'col' = any(select jsonb_object_keys(to_jsonb(new))) then
    raise notice 'column col exists';
...
klin
  • 112,967
  • 15
  • 204
  • 232
  • 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
2

Try it and handle exceptions:

BEGIN
   IF NEW.a >= 5 ...
EXCEPTION
   WHEN undefined_column THEN
      [column "a" does not exist]
END;
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • This should be the accepted answer, because the other options from above performe "complex" computations to find out if the column is there or not, while this solutions just tries it out, and if it fails we know its not there. Ofc you can argue that the "complex" compututations is everything but complex. If its just a few nano secs that can be saved do it. – iOSonntag Jul 22 '23 at 13:09
  • @iOSonntag Thanks. In fairness, I want to add that creating a subtransaction, as my answer does, is not entirely free of cost either. – Laurenz Albe Jul 24 '23 at 02:16