PostgreSQL 9.4 Functions – Loop Over String Literals

functionspostgresql-9.4string

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?

Best Answer

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$;
Related Question