Loop over string literals as input to function PostgreSQL 9.4

Question

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?

Answer 1

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

• dw8547: 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.
• shx: In my environment (postgresql 9.5 on CentOS 7.2), both functions can be ran well.