current community

your communities

more stack exchange communities

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

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
$$
Begin
Alter table points alter COLUMN building set default b;
Alter table points alter COLUMN floor set default f;
End;
$$
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?

share|improve this question
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

1 Answer 1

up vote 1 down vote

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 $$
BEGIN
IF NEW.floor ISNULL THEN
    NEW.floor :=(select floor from buildings where my_current_location='yes');
END IF;

IF NEW.building ISNULL THEN                                         
    NEW.building :=(select name from buildings my_current_location='yes');      
END IF;
RETURN NEW;
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();
share|improve this answer
    
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

 
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.