Postgres array of objects error `error: could not determine polymorphic type because input has type "unknown"`

Question

I have been trying to loop over an array of objects in postgres. However, I repeatedly receive the error error: could not determine polymorphic type because input has type "unknown". I think it has to do with the value I am passing into the function which looks like so.

[
  {
    "number": 1,
    "letter": "a"
  },
  {
    "number": 2,
    "letter": "b"
  }
]

The function is as follows. I have made the type ANYARRAY which I assume should do exactly as the name implies. I would not mind a more specific option is available.

CREATE OR REPLACE FUNCTION "CheckArrayOfObjects" (
  "@arrayOfObjects"           ANYARRAY
)
RETURNS void AS
$func$
DECLARE "e" JSONB;
BEGIN
  FOR "e" IN json_array_elements("@arrayOfObjects")
  LOOP
    RAISE NOTICE 'Checking Item %', "e";
  END LOOP;
END;
$func$ LANGUAGE PLPGSQL;

I have tried a FOREACH as well but it didn't pan out any better. I think the issue lies in the values being passed in so not really sure if modifying the loops will do anything as of now.

Answer 1

The type of the argument should be JSONB (or JSON):

CREATE OR REPLACE FUNCTION "CheckArrayOfObjects" ("@arrayOfObjects" JSONB)
RETURNS void AS
$func$
DECLARE "e" JSONB;
BEGIN
  FOR "e" IN SELECT jsonb_array_elements("@arrayOfObjects")
  LOOP
    RAISE NOTICE 'Checking Item %', "e";
  END LOOP;
END;
$func$ LANGUAGE PLPGSQL;

Example use:

SELECT "CheckArrayOfObjects"(
'[
  {
    "number": 1,
    "letter": "a"
  },
  {
    "number": 2,
    "letter": "b"
  }
]')

NOTICE:  Checking Item {"letter": "a", "number": 1}
NOTICE:  Checking Item {"letter": "b", "number": 2}

---

UPDATE

Where do notices get logged with SQL?

It depends on the server configuration parameter log_min_messages (enum)

What is the type ANYARRAY for if not for arrays?

It's for Postgres array types, e.g. text[], int[] etc. Json array is not a Postgres array, see this answer.