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.
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:
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'))