Calling a function inside a loop in PostgreSQL

Question

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;

Edouard · Accepted Answer · 2022-01-27 14:14:02Z

1

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 ;
$$ ;

edited Jan 27, 2022 at 14:14

answered Jan 26, 2022 at 16:28

Edouard

7,1291 gold badge11 silver badges24 bronze badges

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

8 Comments

gwydion93 Over a year ago

In regards to the language, does it matter that the function will live inside of a PosgreSQL database/schema?

Edouard Over a year ago

No matter, PostGres is compliant with sql obviously, and with some procedural languages : pl/pgsql, pl/python, pl/perl, ... Then sql is usually more performant.

gwydion93 Over a year ago

I am getting an ERROR: return type mismatch in function declared to return text when I run the above. What would cause that?

Edouard Over a year ago

The output parameters of the function state_fips_updater may cause the error. Please share this function definition in your question.

gwydion93 Over a year ago

I have added the requested function and some additional info. Note the unused parameter. I may need to amend the is query in the future so I left it in.

|

Collectives™ on Stack Overflow

Calling a function inside a loop in PostgreSQL

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related