CODEWITHSUNDEEP
postgresqlpostgresql-9.2
sachin
sachin

Reputation: 14355

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

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;

Upvotes: 2

Views: 2511

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61516

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;

Upvotes: 2

Related Questions