PostgreSQL query inside a function with tables and other parameters

Question

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;

I think that what you're trying to accomplish can be done without function — performance will be better. — vyegorov
– vyegorov, Commented Oct 9, 2014 at 10:37

Pavel Stehule · Accepted Answer · 2014-10-09 10:40:38Z

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;

answered Oct 9, 2014 at 10:40

Pavel Stehule

46.7k6 gold badges104 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

flatronka Over a year ago

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

Pavel Stehule Over a year ago

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.

Collectives™ on Stack Overflow

PostgreSQL query inside a function with tables and other parameters

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related