CODEWITHSUNDEEP
postgresqlfunctionloops
gwydion93
gwydion93

Reputation: 1923

Calling a function inside a loop in PostgreSQL

I have a function in PostgreSQL that takes 2 parameters; st_abbr, st_fips (the state abbreviation, and the state fips code) called st_fips_updater.

CREATE OR REPLACE FUNCTION pop_allocation_sql.st_fips_updater(
    st_abbr text,
    st_fips 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;

Note: This function has an unused parameter called st_fips that I am not currently using but may need in the future. I have another function that loops through a json object of state fips codes and abbreviations and prints them out as al: 01, ak: 02 etc... (both values are text).

CREATE OR REPLACE FUNCTION pop_allocation_sql.showstatefips( input jsonb) 
RETURNS SETOF text
AS 
    $$
    DECLARE
        fips text;
        state text;
    BEGIN
       FOR fips, state IN 
       SELECT * FROM  jsonb_each_text($1)
       LOOP
         RETURN NEXT format('%s: %s', lower(state), fips);
       END LOOP;
    END;
$$
LANGUAGE plpgsql;

SELECT op_allocation_sql.showstatefips('{"01": "AL", "02": "AK", "04": "AZ", "05": "AR", "06": 
"CA", "08": "CO", "09": "CT", "10": "DE", "11": "DC", "12": "FL",
 "13": "GA", "15": "HI", "16": "ID", "17": "IL", "18": "IN",
 "19": "IA", "20": "KS", "21": "KY", "22": "LA", "23": "ME",
 "24": "MD", "25": "MA", "26": "MI", "27": "MN", "28": "MS",
 "29": "MO", "30": "MT", "31": "NE", "32": "NV", "33": "NH",
 "34": "NJ", "35": "NM", "36": "NY", "37": "NC", "38": "ND",
 "39": "OH", "40": "OK", "41": "OR", "42": "PA", "44": "RI",
 "45": "SC", "46": "SD", "47": "TN", "48": "TX", "49": "UT",
 "50": "VT", "51": "VA", "53": "WA", "54": "WV", "55": "WI",
 "56": "WY"}');

How can I call my first function (state_fips_updater(st_abbr, st_fips)) inside this loop? I tried the following but got an error ERROR: query has no destination for result data:

CREATE OR REPLACE 
FUNCTION pop_allocation_sql.showstatefips( input jsonb) 
  RETURNS VOID
AS 
$$
DECLARE
  fips text;
  state text;
BEGIN
  FOR fips, state IN 
  SELECT * FROM  jsonb_each_text($1)
  LOOP
    SELECT pop_allocation_sql.st_fips_updater(lower(state), fips);
  END LOOP;
END;
$$
LANGUAGE plpgsql;

Upvotes: 0

Views: 718

Answers (1)

Edouard
Edouard

Reputation: 7065

You should write your function showstatefips in sql instead of plpgsql, this will be more readable and more performant. You can try this :

CREATE OR REPLACE FUNCTION pop_allocation_sql.showstatefips(input jsonb) 
  RETURNS VOID LANGUAGE sql AS
$$
  SELECT pop_allocation_sql.st_fips_updater(lower(i.value), i.key)
    FROM jsonb_each_text(input) AS i ;
$$ ;

Upvotes: 1

Related Questions