Make column default a query

Is there a way I can set up a column in PostgreSQL 9.2 such that the default value is a query? I am having to track down query statements from different sources that don’t respect the “default” behavior that would like to be seen in the various projects that are writing to the DB.

INSERT INTO Person(name, token, priv, sc)
VALUES ('Frank Josephson', '**fake token**', 'user'
      , (SELECT COUNT(*) FROM Person WHERE name='Frank Josephson'));

Otherwise, it’s just me auditing a bunch of excel sheets and applications to make sure this new directive goes through. Any way to set this up as a trigger without running into race conditions?

Here is how you can achieve that using a trigger:

CREATE OR REPLACE FUNCTION default_sc_value()
  RETURNS trigger AS
$BODY$begin
if tg_op='INSERT' then
SELECT COUNT(*) FROM person WHERE name=new.name into new.sc;
end if;

return new;
end;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

and you can associate this trigger to your person table with:

CREATE TRIGGER before_insert_trigger
  BEFORE INSERT
  ON person
  FOR EACH ROW
  EXECUTE PROCEDURE default_sc_value();

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 “generated column”.

Example for “generated column”

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?

Maybe try to create a function that returns the value of count(*) and use that in the insert query.

CREATE FUNCTION mycount(personname varchar) RETURNS integer AS $$
declare
retval integer;
begin
SELECT COUNT(*) FROM Person WHERE name=personname into retval;
return retval;
end;
$$ LANGUAGE plpgsql;

then use it in the query:

INSERT INTO Person(name, token, priv, sc)
VALUES ('Frank Josephson', '**fake token**', 'user', mycount('Frank Josephson'))