September 2nd, 2012, 09:58 AM
Using Function to reduce column value
I am currently trying to use a function to reduce the value of a particular column in a view I have created.
The function is as follows:
create type holder as (name1 text, title1 text, count1 int);
create or replace function coauthorcount1() returns setof holder1 as
for r in select name, title, count
from nametitlecount LOOP
r.count1 := r.count1 - 1;
The table consists of 3 columns: Name, Title and Count.
I am trying to decrement the count values by 1 but it doesn't seem to be working when I run the function using 'select 'coauthorcount();'.
I just get an output of '(0 rows)'.
Any help would be greatly appreciated.
September 2nd, 2012, 10:08 AM
First: you are not doing any UPDATEs in that function, therefor nothing is being changed.
Secondly: there is absolutely no need to use a function for this
Just run a simple update on your table:
is all you need.
SET count = count - 1;
Whenever you find yourself doing loops to do something in a relational database, stop and question the approach.
Databases are not designed for row-by-row processing. They are very efficient in hanlding sets of data.
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
Tips on how to ask better questions:
September 2nd, 2012, 07:49 PM
Many thanks for your response. I tried proceeding as you advised but now receive the message:
"Error: cannot update a view"
"HINT: You need an unconditional ON UPDATE DO INSTEAD rule"