PostgreSQL trigger to generate codes for multiple tables dynamically

I'd like to generate codes for many tables in the database, and stopped to refactor my solution when I was ready to write my third implementation of "get code for table X".

My code is this:

-- Tenants receive a code that's composed of a portion of their subdomain and a unique number.
-- This number comes from this sequence.
CREATE SEQUENCE tenant_codes_seq MAXVALUE 9999 NO CYCLE;

CREATE TABLE tenants (
    subdomain   varchar(36) NOT NULL UNIQ开发者_JAVA百科UE
  , tenant_code char(8)     NOT NULL UNIQUE
  , PRIMARY KEY (tenant_code)
);

-- This function expects four parameters:
-- 1. The column that's receiving the generated code (RECEIVING_COLUMN_NAME)
-- 2. The column that's used to salt the code (SALT_COLUMN_NAME)
-- 3. The number of characters to use from the salt column (SALT_LENGTH)
-- 4. The sequence name, but defaults to RECEIVING_COLUMN_NAME || 's'
CREATE OR REPLACE FUNCTION generate_table_code() RETURNS trigger AS $$
DECLARE
  receiving_column_name text;
  salt_column_name      text;
  salt_length           text;
  sequence_name         text;
BEGIN
  receiving_column_name := TG_ARGV[0];
  salt_column_name      := TG_ARGV[1];
  salt_length           := TG_ARGV[2];

  CASE
  WHEN TG_NARGS = 3 THEN
    sequence_name := receiving_column_name || 's';
  WHEN TG_NARGS = 4 THEN
    sequence_name := TG_ARGV[3];
  ELSE
    RAISE EXCEPTION '3 or 4 arguments expected, received %', TG_NARGS;
  END CASE;

  -- The intent is to return ABC-0001 when salt_column contains 'ABC'
  EXECUTE 'rpad(substr('                ||
          quote_ident(salt_column_name) ||
          ', 1, 4), 4, '                ||
          quote_literal('-')            ||
          ') || lpad(nextval('          ||
          quote_literal(sequence_name)  ||
          ')::text, '                   ||
          quote_literal(salt_length)    ||
          ', '                          ||
          quote_literal('0')            ||
          ')'
  INTO STRICT NEW;
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER generate_tenant_code_trig
  BEFORE INSERT ON tenants FOR EACH ROW
  EXECUTE PROCEDURE generate_table_code('tenant_code', 'subdomain', 4);

How do I assign to NEW.tenant_code, NEW.user_code or NEW.table_whatever_code?

Running some tests yields the correct "statement", but I can't seem to assign correctly:

INSERT INTO tenants(subdomain) VALUES ('abc')


CREATE TABLE
ERROR:  syntax error at or near "NEW"
LINE 1: NEW.tenant_code := rpad(substr(subdomain, 1, 4), 4, '-') || ...
        ^
QUERY:  NEW.tenant_code := rpad(substr(subdomain, 1, 4), 4, '-') || lpad(nextval('tenant_codes')::text, '4', '0'::text)
CONTEXT:  PL/pgSQL function "generate_table_code" line 20 at EXECUTE statement

I'd be quite enthusiastic to be shown wrong (I occasionally need this myself too), but best I'm aware, referring column names using variables is one of those cases where you actually need to use PL/C triggers rather than PL/PgSQL triggers. You'll find examples of such triggers in contrib/spi and on PGXN.

Alternatively, name your columns consistently so as to be able to reference them directly, e.g. NEW.tenant_code.

Personally, I generally end up writing a function that creates the trigger:

create function create_tg_stuff(_table regclass, _args[] text[])
  returns void as $$
begin
  -- explore pg_catalog a bit
  execute $x$
  create function $x$ || quote_ident(_table || '_tg_stuff') || $x$()
    returns trigger as $t$
  begin
    -- more stuff
    return new;
  end;
  $t$ language plpgsql;
  $x$;
end;
$$ language plpgsql;

NEW is type RECORD, so you can't assign to that AFAIK.

To set the value of a column, assign to NEW.column, for example:

NEW.tenant_code := (SELECT some_calculation);

Maybe your design is too complicated; PL/SQL is a very limited language - try to make your code as simple as possible