0

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?

Improve this question
3
  • 1
    Exactly, you cannot do that. But really, why do you want this? Commented Oct 11, 2013 at 10:32
  • 1
    (If you really need this, you can build a dynamic SQL and execute it.) Commented Oct 11, 2013 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. Commented Oct 12, 2013 at 2:27

1 Answer 1

Reset to default
1

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();
Improve this answer
2
  • 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. Commented Oct 12, 2013 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. Commented Oct 12, 2013 at 16:11

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.