Skip site navigation (1) Skip section navigation (2)

Re: Problem adding columns

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Oscar Alberto Chavarria Marin <cyberbuzzard(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Problem adding columns
Date: 2007-02-12 04:25:06
Message-ID: 20070212042506.GA12529@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-novice
On Sun, Feb 11, 2007 at 09:16:36PM -0600, Oscar Alberto Chavarria Marin wrote:
> CREATE FUNCTION
> add_reimburse(integer,integer,integer,integer,integer,integer,integer)

Why does the function take all these parameters?  It doesn't do anything
with them.

>    SELECT count(*) INTO client_count FROM investments;
>    FOR counter IN 1 TO client_count
>    LOOP

Is this the actual code?  CREATE FUNCTION fails with a syntax error
in 8.0 and later, which presumably you're using since the function
has dollar quotes.  Perhaps you meant to do this (although a simpler
solution exists; see later):

FOR sumyields IN SELECT * FROM investments LOOP

>        yields:=
> sumyields.return1+sumyields.return2+sumyields.return3+sumyields.return4+sumyields.return5+sumyields.return6+sumyields.return7
> ;

The table definition you showed doesn't have NOT NULL constraints
on these columns -- are any of the values NULL?  If so then their
sum will be NULL.  You might need to use COALESCE:

yields := COALESCE(sumyields.return1, 0) +
          COALESCE(sumyields.return2, 0) +
	  ...

>        INSERT INTO totalreturns(total_reimburse) VALUES(yields);
>        yields:=0;

There's no need to set yields to 0 at the end of the loop when
you're going to be setting it at the beginning of the next loop.

>    END LOOP;
> return counter;
> END;
> $$ language plpgsql;

Are you aware of INSERT ... SELECT?  It looks like you're trying to
do this:

INSERT INTO totalreturns (total_reimburse)
SELECT COALESCE(return1, 0) + COALESCE(return2, 0) + COALESCE(return3, 0) +
       COALESCE(return4, 0) + COALESCE(return5, 0) + COALESCE(return6, 0) +
       COALESCE(return7, 0)
  FROM investments;

In PL/pgSQL you can get the number of affected rows with GET DIAGNOSTICS:

GET DIAGNOSTICS counter := ROW_COUNT;

-- 
Michael Fuhr

In response to

pgsql-novice by date

Next:From: Matthew CampbellDate: 2007-02-12 06:27:28
Subject: GiST Comparing IndexTuples/Datums
Previous:From: Oscar Alberto Chavarria MarinDate: 2007-02-12 03:16:36
Subject: Problem adding columns

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group