Declare a variable as a ROWTYPE - Trigger function

Question

In this problem, I've created a table called groups(id, name, group_id INTEGER NULLABLE,class_id INTEGER NULLABLE), the logic is to classify the data between groups, classes and subclasses. A group has many classes, and a class has many subclasses. I thought to make a trigger function in plpgsql that guarantees some conditions before insert or update data.

The conditions to classify the data are below.

• Groups:
  the user needs to insert only the name value.
  group_id and clazz_id must be NULL values.
  

• Classes:
  the user needs to insert only the name and group_id values.
  group_id must be an INTEGER NOT NULL value and clazz_id is a NULL VALUE.
  group_id must attend the conditions to classificate as a Group.
  

• Subclasses:
  The user needs to insert only the name, group_id and clazz_id values. Thus, you can set the group_id value equals to the group_id who belongs to the clazz_id for guarantee that the condition is true.
  group_id must be the same group_id as the class_id setted.
  clazz_id must attend to the conditions to classificate as a Class.
  

.

CREATE FUNCTION emp_subclasse() RETURNS trigger AS $subclasse_verification$
DECLARE
  group groups%ROWTYPE;
  classe groups%ROWTYPE;
  class_group groups%ROWTYPE;
BEGIN
  group =(SELECT * FROM groups WHERE id=NEW.group_id);
  classe =(SELECT * FROM groups WHERE id=NEW.clazz_id);
  class_group =(SELECT * FROM groups WHERE id=classe.group_id);
  IF NEW.clazz_id IS NOT NULL THEN
      IF (classe.group_id) IS NULL THEN
          RAISE EXCEPTION 'A CLASS MUST POINT TO A GROUP';
      ELSIF classe.group_id IS NOT NULL THEN
          NEW.group_id = classe.group_id;
          IF class_group.group_id IS NOT NULL OR class_group.clazz_id IS NOT NULL THEN
              RAISE EXCEPTION 'A GROUP CANNOT HAVE A GROUP_ID OR CLASS_ID POINTED';
          END IF;
      END IF;
      IF classe.clazz_id IS NOT NULL THEN
          RAISE EXCEPTION 'CANNOT CLASSIFY A CLASS AS A SUBCLASS';
      END IF;
  ELSIF NEW.clazz_id IS NULL THEN
      IF NEW.group_id IS NOT NULL THEN
          IF group.group_id IS NOT NULL OR grupo.clazz_id IS NOT NULL THEN
              RAISE EXCEPTION 'A GROUP CANNOT HAVE A GROUP_ID OR CLASS_ID POINTED';
          END IF;
      END IF;
  END IF;
END;
$subclasse_verification$ LANGUAGE 'plpgsql';

CREATE TRIGGER subclasse_verification BEFORE INSERT OR UPDATE ON groups
  FOR EACH ROW EXECUTE PROCEDURE emp_subclasse();

However, when I try to insert values, I get the error [2018-11-20 16:35:20] [42601] ERROR: subquery must return only one column [2018-11-20 16:35:20] WHERE: PL/pgSQL function emp_subclasse() line 7 at assignment

Answer 1

  group =(SELECT * FROM groups WHERE id=NEW.group_id);
  classe =(SELECT * FROM groups WHERE id=NEW.clazz_id);
  class_group =(SELECT * FROM groups WHERE id=classe.group_id);

These subqueries are returning all columns (SELECT *). To populate the variables correctly, either select the row without expanding: group := (SELECT groups FROM groups WHERE ...) or do a SELECT INTO: SELECT * INTO group FROM groups WHERE ...