Using a loop to plug parameters into a function in PostgreSQL

Question

Suppose I have an array of state abbreviations like this:

states text[] := ARRAY['al', 'ak', 'az', 'ar', 'ca', 
'co', 'ct', 'de', 'dc', 'fl', 'ga', 'hi', 'id', 'il', 
'in', 'ia', 'ks', 'ky', 'la', 'me', 'md', 'ma', 'mi', 
'mn', 'ms', 'mo', 'mt', 'ne', 'nv', 'nh', 'nj', 'nm', 
'ny', 'nc', 'nd', 'oh', 'ok', 'or', 'pa', 'ri', 'sc', 
'sd', 'tn', 'tx', 'ut', 'vt', 'va', 'wa', 'wv', 'wi', 
'wy', 'pr', 'vi'];

I want to loop or iterate through this array list and plug the individual state abbreviations into a function as a parameter and run it. Note: I also have the same data in a table called states under the same schema with a column called st_abbr. Here's the function:

CREATE OR REPLACE FUNCTION pop_allocation_sql.st_fips_updater2(
    st_abbr text
 )
    RETURNS VOID   
  AS
$$
DECLARE 

BEGIN
 -- logic
 EXECUTE format(
    'UPDATE pop_allocation_output_12102021.%s_population_allocation_20210202
        SET st_fips = LEFT(geoid, 2)
        WHERE st_fips <> LEFT(geoid, 2)',
  st_abbr);
END;
$$ LANGUAGE plpgsql
VOLATILE;

How would I do that?

"How would I do that?" -- The best you can do is to revise that schema and unite those several tables into one with columns for the country and day. — sticky bit
– sticky bit, Commented Jan 27, 2022 at 14:14

user330315 · Accepted Answer · 2022-01-27 14:14:47Z

1

No need to use a loop, you can use unnest() to generate one row for each array element.

select pop_allocation_sql.st_fips_updater2(state)
from unnest(ARRAY['al', 'ak', 'az', 'ar', 'ca', 
                 'co', 'ct', 'de', 'dc', 'fl', 'ga', 'hi', 'id', 'il', 
                 'in', 'ia', 'ks', 'ky', 'la', 'me', 'md', 'ma', 'mi', 
                 'mn', 'ms', 'mo', 'mt', 'ne', 'nv', 'nh', 'nj', 'nm', 
                 'ny', 'nc', 'nd', 'oh', 'ok', 'or', 'pa', 'ri', 'sc', 
                 'sd', 'tn', 'tx', 'ut', 'vt', 'va', 'wa', 'wv', 'wi', 
                 'wy', 'pr', 'vi']) as t(state);

If those abbreviations are available in a different table, there is no need to use an array or a variable:

select pop_allocation_sql.st_fips_updater2(st_abbr)
from states

But your data model seems rather strange. You shouldn't have one table for each state, but a single table where state is a column.

answered Jan 27, 2022 at 14:14

user330315

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

1 Comment

gwydion93 Over a year ago

I went with your second suggestion using the table and worked like a charm. I feel like I wasted time trying to iterate through an array when I already had the data in a table. BTW - this function is a 'clean-up' for existing tables. But I took your single table comment to heart from another answer you gave and redid my new workflow to be single national tables.

Collectives™ on Stack Overflow

Using a loop to plug parameters into a function in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related