CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1
flatronka
flatronka

Reputation: 1081

PostgreSQL query inside a function with tables and other parameters

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;

Upvotes: 0

Views: 307

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45930

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;

Upvotes: 1

Related Questions