0

I try to update a table based on some criteria, but unfortunately the table names can change so I created a function which go trough 2 arrays and updates table according the arrays and the other table values.

Basically I wan't to fix the country codes for small countries which has no ISO standard short names.

My problem that I have some syntactical problem in my queries, nothing else. Is it the right way for implementing that query.

Your help is appropriated. Thanks. EDIT: The final working version of the function:(Please don't hesitate to suggest some improvements)

CREATE OR REPLACE  FUNCTION fixCountryCodes(destinationTable text, sourceTable text)
RETURNS void
AS $$
DECLARE 
    countryNames varchar[] := ARRAY['ALD', 'BJN', 'CNM', 'CYN', 'ESB', 'IOA', 'ISR', 'KAB', 'KAS', 'NOR', 'PSX', 'SCR', 'SDS', 'SER', 'SOL', 'UMI', 'USG', 'WSB'];
    rightCountryCodes varchar[] := array['FI', 'CO', 'CY', 'TU', 'UK', 'AU', 'IS', 'KZ', 'KZ', 'NO', 'WE', 'CH', 'OD', 'CO', 'SO', 'US', 'US', 'UK'];
BEGIN
    FOR I IN 1..array_upper(countryNames, 1) LOOP
        EXECUTE 'UPDATE ' || destinationTable || ' s ' || 'SET name = '''|| rightCountryCodes[i] || ''' FROM ' || sourceTable || ' n WHERE n.adm0_a3 LIKE ''' || countryNames[i] || ''' AND n.gid = s.id';
    END LOOP;
END
$$ LANGUAGE plpgsql;
flatronka
  • 1,061
  • 25
  • 51

1 Answers1

1

There is wrong syntax of CREATE OR REPLACE FUNCTION. You have to use a syntax:

CREATE FUNCTION name(params)
RETURNS void -- in your case 
AS $$
DECLARE ...
BEGIN
END;
$$ LANGUAGE plpgsql;
Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • thanks this was on of my problem, I fixed the query you can check the version above, please don't hesitate to give me any idea to improve it – flatronka Oct 09 '14 at 11:23
  • There is one possible and important significant improvement - remove outer FOR cycle. You can store these data in aux table, and then you can run only one update for all. If you run this function once only, then a performance probably is not terribly important. Next you can use a "format" function instead building string. It is more readable. Second - don't use a dynamic SQL (EXECUTE) statement without parameter escaping. Your example is a SQL injection vulnerable. The plpgsql documentation contains nice examples how to do it safely. – Pavel Stehule Oct 09 '14 at 11:29