Explore our sites

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question
 
How about using Triggers ? –  Houari 2 days ago
 
@Houari I'd be ok with a trigger as long as it didn't induce a race condition. Then again, I might just be ok with it if it did. Post an answer. Hell, even "No, this isn't possible but a work around is..." would be great. –  wheaties 2 days ago
 
Please see my response –  Houari 2 days ago
add comment

3 Answers

up vote 1 down vote accepted

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();
share|improve this answer
add comment
up vote 2 down vote

I have read your explanation several times and failed to comprehend what you are trying to do exactly. So this is an educated guess.

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.

share|improve this answer
 
No, I don't want a view, I want an INSERT such that the sc column contains the Nth value of that name inserted into the TABLE. –  wheaties 2 days ago
 
@wheaties: The "Nth time at the time of insert", not to be changed by any subsequent changes to the table? Then my guess was wrong. This comment would have been instrumental in the question. –  Erwin Brandstetter 2 days ago
add comment
up vote 1 down vote

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'))
share|improve this answer
 
I accepted the trigger answer because I can do that on my end without having to find every single app which is "doing it wrong." –  wheaties 2 days ago
 
No prob! If that is the solution for you it is good you accepted it. Cheers! Actually forgot also to mention that you could have used the function in the table itself by setting it as default value. –  Fabrizio Mazzoni 2 days ago
 
you could? That will avoid a race condition. –  wheaties 2 days ago
 
How would I make a 1 argument function the default for a column? Could you add the CREATE TABLE with your answer? –  wheaties 2 days ago
1  
Do excuse me as I made a terrible mistake in the comment. You cannot use a column as an argument in a function as default. please do excuse me!!!!! –  Fabrizio Mazzoni 2 days ago
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.