"null value in column violates not-null constraint" error in PostgreSQL

Question

A simplified schema representing the issue I'm having is as follows:

CREATE TABLE a (
   var1 text PRIMARY KEY
);

CREATE TABLE b (
   var1 text,
   var2 text PRIMARY KEY
);

CREATE TABLE c (
   var1 text,
   var2 text,
   var3 text PRIMARY KEY
);

In table c, var1 and var2 represent keys in tables a and b respectively.

From these values, we wish to generate a key for the new tuple being inserted into c. We do so by creating a trigger calling a function as follows:

CREATE TRIGGER key_Gen 
    BEFORE INSERT ON c 
    FOR EACH ROW 
    EXECUTE PROCEDURE key_Gen();

CREATE FUNCTION key_Gen() RETURNS trigger AS $key_Gen$
    BEGIN
        -- Check that new values are not null
        IF NEW.var1 IS NULL THEN
            RAISE EXCEPTION 'var1 cannot be null';
        END IF;

        IF NEW.var2 IS NULL THEN
            RAISE EXCEPTION 'var2 cannot be null';
        END IF;

        INSERT INTO a VALUES (NEW.var1);
        INSERT INTO b VALUES (NEW.var1 || NEW.var2);
        INSERT INTO c VALUES (NEW.var1 || NEW.var2 || NEW.var3);

        RETURN NEW;
    END;
$key_Gen$ LANGUAGE plpgsql;

• For table a, the new key should be var1.
• For table b, the new key should be a concatenation of var1 and var2.
• For table c, the new key should be a concatenation of var1, var2, and var3.

Let's say I insert the following:

INSERT INTO c VALUES ( 'TEST', 'HAS', 'PASSED');

I get the following error message:

null value in column "var2" violates not-null constraint

I've tried changing various things around in the trigger and function, such as changing BEFORE INSERT to AFTER INSERT, but that doesn't affect its behavior.
I've also tried referencing the documentation and various other examples, but none of the one's I've seen address the particular conditions of this problem.

Can what I want to do with my function even be done?

Answer 1

Your trigger would work like this:

CREATE OR REPLACE FUNCTION key_gen()
  RETURNS trigger AS
$func$
BEGIN
-- Check that new values are not null
IF NEW.var1 IS NULL THEN
    RAISE EXCEPTION 'var1 cannot be null';
END IF;

IF NEW.var2 IS NULL THEN
    RAISE EXCEPTION 'var2 cannot be null';
END IF;

INSERT INTO a(var1)       VALUES (NEW.var1);
INSERT INTO b(var1, var2) VALUES (NEW.var1, NEW.var1 || NEW.var2);

NEW.var3 := (NEW.var1 || NEW.var2 || NEW.var3);

RETURN NEW;

END
$func$ LANGUAGE plpgsql;

Two problems:

• Infinite loop, because you start another INSERT INTO c .... Just assign NEW.var3 instead.

• Your INSERT statements should generally have target lists for persisted statements! Then you wouldn't have missed the illegal statement for b, where you inadvertently assign var1 and leave the pk column NULL.

But I would discourage to use this design at all.

One query

Drop the trigger and use this query with data-modifying CTEs instead. Does everything you asked for:

WITH val AS (
   SELECT 'TEST'::text   AS v1 -- insert values once
         ,'HAS'::text    AS v2
         ,'PASSED'::text AS v3
   )
, ins AS (
   SELECT v1             AS var1
         ,v1 || v2       AS var2
         ,v1 || v2 || v3 AS var3
   FROM   val
   WHERE  v1 IS NOT NULL
   AND    v2 IS NOT NULL
   AND    v3 IS NOT NULL
   )
, a AS (INSERT INTO a(var1)             SELECT var1             FROM ins)
, b AS (INSERT INTO b(var1, var2)       SELECT var1, var2       FROM ins)
        INSERT INTO c(var1, var2, var3) SELECT var1, var2, var3 FROM ins
RETURNING *;

The final RETURNING clause is optional.

More explanation, links and details in this related answer:
INSERT rows into multiple tables in a single query, selecting from an involved table

Function

You can easily wrap this into an SQL function, if you want:

CREATE OR REPLACE function f_tripple_ins(v1 text, v2 text, v3 text)
  RETURNS void AS
$func$
WITH ins AS (
   SELECT v1             AS var1
         ,v1 || v2       AS var2
         ,v1 || v2 || v3 AS var3
   WHERE  v1 IS NOT NULL
   AND    v2 IS NOT NULL
   AND    v3 IS NOT NULL
   )
, a AS (INSERT INTO a(var1)             SELECT var1             FROM ins)
, b AS (INSERT INTO b(var1, var2)       SELECT var1, var2       FROM ins)
        INSERT INTO c(var1, var2, var3) SELECT var1, var2, var3 FROM ins
$func$ LANGUAGE sql;

Works for Postgres 9.2 or later.
In Postgres 9.1 or earlier, you have to use $1, $2, $3 to refer to input parameters in an SQL function.

-> SQLfiddle demo.

Answer 2

Shouldn't the insert part look like this:

INSERT INTO a VALUES (NEW.var1);
INSERT INTO b VALUES (NEW.var1, New.var1 || NEW.var2);
INSERT INTO c VALUES (NEW.var1, NEW.var2, NEW.var1 || NEW.var2 || NEW.var3);