1

I have a function called update_total which looks like this:

CREATE OR REPLACE FUNCTION update_total (table_name CHARACTER VARYING, year CHARACTER(4), donor_type CHARACTER VARYING, donor_code CHARACTER(5)) RETURNS VOID AS $$

DECLARE

table_name ALIAS FOR $1;
year ALIAS FOR $2;
donor_type ALIAS FOR $3;
donor_code ALIAS FOR $4;
query_statement TEXT;

BEGIN

    query_statement :=
    '
    UPDATE gha.' || table_name  || '
    SET "' || year || '" = "' || year ||'_total"
    FROM gha.' || table_name || ' "' || year || '",
    (
        SELECT
        donor_type
        , COALESCE(SUM("' || year || '"), 0) AS "' || year ||'_total"
        FROM gha.' || table_name || '
        WHERE donor_type = ''' || donor_type || '''
        GROUP BY donor_type
    ) "new_data"
    WHERE
    gha.' || table_name || '.donor_code = ' || CAST(donor_code AS INT) || ';'
    ;

    EXECUTE query_statement;

END;
$$ LANGUAGE plpgsql;

It updates a row in a table that holds an aggregate value and I call it, for example, like this:

SELECT update_total ('bilateral_oda_dac_1', '1990', 'Multilateral', '20002');

The table it updates is set up so that there are columns for each year in a range, i.e.:

   Column   |       Type        
------------+-------------------
 donor_code | smallint           
 donor_name | character varying 
 donor_type | character varying  
 1990       | numeric            
 1991       | numeric      
 1992       | numeric  
...

With this function I can only update the aggregate value in one column at at time, but I would like to be able to update a range of columns at once, something along the lines of:

CREATE FUNCTION update_all_total ()
    RETURNS void LANGUAGE plpgsql AS

$BODY$
BEGIN

FOR i IN 1990 .. 1995
LOOP
   PERFORM update_total ('bilateral_oda_dac_1', i, 'DAC', '20001');
END LOOP;

END;
$BODY$;

(I used this post to get me started: https://stackoverflow.com/questions/11164409/sql-call-function-multiple-times-in-a-loop-postgres-8-3). The code snipped above doesn't work because the function I am using takes a string as input and I'm passing it an integer. How can I set the loop up so that I pass strings to the function that I am calling inside it?

2

You can use to_char() ,

CREATE FUNCTION update_all_total () RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
FOR i IN 1990 .. 1995
LOOP
   PERFORM update_total ('bilateral_oda_dac_1', to_char(i, '9999'), 'DAC', '20001');
END LOOP;
END;
$BODY$;

or can use cast ::char(4).

CREATE FUNCTION update_all_total () RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
FOR i IN 1990 .. 1995
LOOP
   PERFORM update_total ('bilateral_oda_dac_1', i::char(4), 'DAC', '20001');
END LOOP;
END;
$BODY$;
| improve this answer | |
  • Casting worked perfectly. I tried the formatting function as well (to_char(i, '9999')) but it converted the column name to " 1990" so this way didn't work. It needs some formatting to get rid of the space it inserts. – dw8547 Apr 1 '16 at 13:38
  • In my environment (postgresql 9.5 on CentOS 7.2), both functions can be ran well. – shx Apr 1 '16 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.