Getting all columns name in an table using trigger function in postgresql

Question

How can I get all column names and their values in trigger function because i need to validate all column values before inserting into the table.I have tried below code.If we know that column name means we can get the value easily by using NEW object in trigger function as NEW.myColumnName.But here I need to get the column name dynamically...

    CREATE FUNCTION insert_update_validate() RETURNS TRIGGER AS $$
    DECLARE
    BEGIN
    FOR i IN 0..(TG_ARGV-1) LOOP
         IF TG_ARGV[i] IS NULL THEN                 
               RAISE EXCEPTION 'cannot have null VALUE', NEW.TG_ARGV[i];
    END LOOP;
    RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;

Daniel Vérité · Accepted Answer · 2013-10-07 15:59:20Z

2

Since hstore is part of PostgreSQL, casting a row to hstore is the primary method to iterate on its columns, at least in a plpgsql context. Otherwise as a language it doesn't provide any construct to extract column names from rows.

Basically it's about iterating over each(store(NEW)). Here's a skeleton you may use:

CREATE FUNCTION insert_update_validate() RETURNS TRIGGER AS $$
DECLARE
 k text;
 v text;
BEGIN
  FOR k,v IN select key,value from each(hstore(NEW)) LOOP
    if v is null then
      raise exception 'value is null for column %', k;
    end if;
  END LOOP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

answered Oct 7, 2013 at 15:59

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting all columns name in an table using trigger function in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related