Make column default a query
Most likely a default value or trigger are not suitable solutions. The values inserted automatically are snapshots and quickly invalidated by subsequent writes to the table.
You should use a VIEW or a &quot;generated column&quot;.
Example for &quot;generated column&quot;
All you need is a function that takes the table type as parameter:
CREATE OR REPLACE FUNCTION sc (person)
  RETURNS bigint AS
$func$
SELECT count(*) FROM person WHERE name = $1.name
$func$ LANGUAGE sql STABLE;

Now, you get the current count with
SELECT *, p.sc
FROM   person p
WHERE  name = 'Frank Josephson';

Details in the related answer on SO:

Store common query as column?