title: How to pass a record to a PL/pgSQL function?
body: <p>I have 8 similar PL/pgSQL functions; they are used as <code>INSTEAD OF INSERT/UPDATE/DELETE</code> triggers on views to make them writable. The views each combine columns of one generic table (called "things" in the example below) and one special table ("shaped_things" and "flavored_things" below). PostgreSQL's inheritance feature can't be used in our case, by the way.</p>
  
  <p>The triggers have to insert/update rows in the generic table; these parts are identical across all 8 functions. Since the generic table has ~30 columns, I'm trying to use a helper function there, but I'm having trouble passing the view's <code>NEW</code> record to a function that needs a <code>things</code> record as input.</p>
  
  <p>(Similar questions have been asked <a href="https://stackoverflow.com/questions/12716024/passing-a-record-as-function-argument-pl-pgsql">here</a> and <a href="https://stackoverflow.com/questions/3880210/how-to-pass-a-record-as-parameter-for-pl-pgsql-function">here</a>, but I don't think I can apply the suggested solutions in my case.)</p>
  
  <h3>Simplified schema</h3>
  
  <pre><code>CREATE TABLE things (
      id    SERIAL  PRIMARY KEY,
      name  TEXT    NOT NULL
      -- (plus 30 more columns)
  );
  CREATE TABLE flavored_things (
      thing_id  INT   PRIMARY KEY REFERENCES things (id) ON DELETE CASCADE,
      flavor    TEXT  NOT NULL
  );
  CREATE TABLE shaped_things (
      thing_id  INT   PRIMARY KEY REFERENCES things (id) ON DELETE CASCADE,
      shape     TEXT  NOT NULL
  );
  -- etc...
  </code></pre>
  
  <h3>Writable view implementation for flavored_things</h3>
  
  <pre><code>CREATE VIEW flavored_view AS
      SELECT t.*,
             f.*
        FROM things t
        JOIN flavored_things f ON f.thing_id = t.id;
  
  CREATE FUNCTION flavored_trig () RETURNS TRIGGER AS $fun$
  DECLARE
      inserted_id  INT;
  BEGIN
      IF TG_OP = 'INSERT' THEN
          INSERT INTO things VALUES (  -- (A)
              DEFAULT,
              NEW.name
              -- (plus 30 more columns)
          ) RETURNING id INTO inserted_id;
          INSERT INTO flavored_things VALUES (
              inserted_id,
              NEW.flavor
          );
          RETURN NEW;
      ELSIF TG_OP = 'UPDATE' THEN
          UPDATE things SET  -- (B)
              name = NEW.name
              -- (plus 30 more columns)
          WHERE id = OLD.id;
          UPDATE flavored_things SET
              flavor = NEW.flavor
          WHERE thing_id = OLD.id;
          RETURN NEW;
      ELSIF TG_OP = 'DELETE' THEN
          DELETE FROM flavored_things WHERE thing_id = OLD.id;
          DELETE FROM things WHERE id = OLD.id;
          RETURN OLD;
      END IF;
  END;
  $fun$ LANGUAGE plpgsql;
  
  CREATE TRIGGER write_flavored
      INSTEAD OF INSERT OR UPDATE OR DELETE ON flavored_view
      FOR EACH ROW EXECUTE PROCEDURE flavored_trig();
  </code></pre>
  
  <p>The statements marked "(A)" and "(B)" above are what I would like to replace with a call to a helper function.</p>
  
  <h3>Helper function for INSERT</h3>
  
  <p>My initial attempt was to replace statement "(A)" with</p>
  
  <pre><code>inserted_id = insert_thing(NEW);
  </code></pre>
  
  <p>using this function</p>
  
  <pre><code>CREATE FUNCTION insert_thing (new_thing RECORD) RETURNS INTEGER AS $fun$
  DECLARE
      inserted_id  INT;
  BEGIN
      INSERT INTO things (name) VALUES (
          new_thing.name
          -- (plus 30 more columns)
      ) RETURNING id INTO inserted_id;
      RETURN inserted_id;
  END;
  $fun$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>This fails with the error message <em>"PL/pgSQL functions cannot accept type record"</em>.</p>
  
  <p>Giving the parameter the type <code>things</code> doesn't work when the function is called as <code>insert_thing(NEW)</code>: <em>"function insert_thing(flavored_view) does not exist"</em>.</p>
  
  <p>Simple casting doesn't seem to be available here; <code>insert_thing(NEW::things)</code> produces <em>"cannot cast type flavored_view to things"</em>. Writing a CAST function for each view would remove what we gained by using a helper function.</p>
  
  <p>Any ideas?</p>