Automatic position when inserting rows

I have a need to make sure that there is always a position value when adding a row to a table. In this case, it’s an attachment position. So this query creates a trigger function (PostgreSQL) that will set the ‘posn’ value appropriately whenever I insert a row.

CREATE OR REPLACE FUNCTION tf_auto_position()
      RETURNS trigger AS
$BODY$
    DECLARE
        nextpos INTEGER;
    BEGIN
        EXECUTE 'SELECT COALESCE(MAX(posn), 0) + 1 FROM ' || TG_TABLE_NAME
            INTO nextpos
        NEW.posn = nextpos;
        RETURN NEW;
    END;
$BODY$
    LANGUAGE plpgsql VOLATILE;

And this is how the trigger is applied to a table:

CREATE TRIGGER ins_attachments
    BEFORE INSERT ON attachments FOR EACH ROW
    EXECUTE PROCEDURE tf_auto_position();

Note that the table must have a ‘posn’ column.

I’ve also had the situation where I’ve had to set the position for rows that are children of another table’s rows. In this case, the EXECUTE statement changes to something like this:

EXECUTE 'SELECT COALESCE(MAX(posn), 0) + 1 FROM ' || TG_TABLE_NAME || ' WHERE foreign_id=$1'
    INTO nextpos
    USING NEW.foreign_id;