#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0

    Using Function to reduce column value


    Hi,

    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
    '
    declare
    r holder1%rowtype;
    begin
    for r in select name, title, count
    from nametitlecount LOOP
    r.count1 := r.count1 - 1;

    end loop;
    return;
    end
    '
    language 'plpgsql';


    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.

    Cheers
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    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:
    Code:
    UPDATE nametitlecount 
      SET count = count - 1;
    is all you need.

    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.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0
    Hi Shammat,

    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"

IMN logo majestic logo threadwatch logo seochat tools logo