I want to set the default value for the column building and floor in my table points in the below function called test(b,f)

Create or replace function test(b text, f text) returns void as
Alter table points alter COLUMN building set default b;
Alter table points alter COLUMN floor set default f;
language plpgsql;

When I run select test('Shopping Mall','03') it gives me below error:

 column b does not exist

It seems that I cannot use a function arguments b in the alter ... query?

  • 1
    Exactly, you cannot do that. But really, why do you want this? – dezso Oct 11 '13 at 10:32
  • 1
    (If you really need this, you can build a dynamic SQL and execute it.) – dezso Oct 11 '13 at 12:58
  • I want to dynamically set my table column default value so every time when I insert row data, there is no need to key in the default value again and again. I have found a way to do this by using trigger. – Xianlin Oct 12 '13 at 2:27

I have found the solution by using function triggers. I didn't dynamically change the default value but I used trigger function to update the column value before insert action. It can achieve the same result.

Create or replace function update_value() 
Returns Trigger AS $$
    NEW.floor :=(select floor from buildings where my_current_location='yes');

IF NEW.building ISNULL THEN                                         
    NEW.building :=(select name from buildings my_current_location='yes');      
END $$ Language 'plpgsql';

Also you need to add a trigger to the table:

Create triggers update_value_trigger Before Insert On points For each row execute procedure update_value();
  • Well, this now makes sense. Changing the default every time, well, it means that the default is not default. Still, are you sure you have to use a trigger? Can't you do this from the INSERT statement? It would be a cleaner solution. – dezso Oct 12 '13 at 9:22
  • I cannot because I am using QGIS software to draw a point with all attributes and write into a table, not using INSERT statement. To do that, I need to hack the QGIS software. – Xianlin Oct 12 '13 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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