up vote 2 down vote favorite

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;
share|improve this question
up vote 2 down vote

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.