PostgreSQL function param json_populate_recordset ARRAY problem

composite-typesjsonparameterpostgresql

Previous question: PostgreSQL function parameters JSON to TYPE

I am trying to create a sample of code using json_populate_recordset, but it seems I am doing something wrong. I have created a test_GPS function:

CREATE OR REPLACE FUNCTION test_gps
  (
    gps_points API_GPS_POINT []
  )
  RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
  api_gps_points API_GPS_POINT;
BEGIN

  FOR i IN 1..array_length(gps_points, 1)
  LOOP
    api_gps_points = gps_points [i];
    RAISE NOTICE 'API_GPS_POINT : %', api_gps_points;

  END LOOP;
END;
$$;

When I try to call it:

SELECT *
FROM test_gps
(
    json_populate_recordset(NULL :: API_GPS_POINT,
                            '[
                              {
                                "gps_id": 300,
                                "gps_street": "24 grand rue",
                                "gps_city": "illkirch",
                                "gps_postalcode": "67120",
                                "gps_type": "(387,GPS_PERSO)",
                                "gps_lat": -51.32541,
                                "gps_lon": 42.80386,
                                "gps_counter": 0,
                                "gps_label": "alcatel"
                              },
                              {
                                "gps_id": 300,
                                "gps_street": "24 grand rue",
                                "gps_city": "illkirch",
                                "gps_postalcode": "67120",
                                "gps_type": "(387,GPS_PERSO)",
                                "gps_lat": -51.32541,
                                "gps_lon": 42.80386,
                                "gps_counter": 0,
                                "gps_label": "alcatel"
                              }
                            ]')
);

I get this error:

[42883] ERROR: function test_gps(api_gps_point) does not exist
Indice : No function matches the given name and argument types. You
might need to add explicit type casts. Position : 15

In the error code, we see that the transformation of data works perfectly, but the parameter isn't detected as an ARRAY, so Postgres doesn't find the function.

SELECT *
FROM json_populate_recordset(NULL :: API_GPS_POINT,
                             '[
                               {
                                 "gps_id": 300,
                                 "gps_street": "24 grand rue",
                                 "gps_city": "illkirch",
                                 "gps_postalcode": "67120",
                                 "gps_type": "(387,GPS_PERSO)",
                                 "gps_lat": -51.32541,
                                 "gps_lon": 42.80386,
                                 "gps_counter": 0,
                                 "gps_label": "alcatel"
                               },
                               {
                                 "gps_id": 300,
                                 "gps_street": "24 grand rue",
                                 "gps_city": "illkirch",
                                 "gps_postalcode": "67120",
                                 "gps_type": "(387,GPS_PERSO)",
                                 "gps_lat": -51.32541,
                                 "gps_lon": 42.80386,
                                 "gps_counter": 0,
                                 "gps_label": "alcatel"
                               }
                             ]');

Result:

300,24 grand rue,illkirch,67120,"(387,GPS_PERSO)",-51.32541,42.80386,0,alcatel
300,24 grand rue,illkirch,67120,"(387,GPS_PERSO)",-51.32541,42.80386,0,alcatel

After this small test, we see json_populate_recordset work well, so how gives this array as a parameter?

How can I resolve it? Do you have some idea?

I try to do that from NodeJS, I would like to pass a list/array of API_GPS_POINTS.

-- auto-generated definition
create type api_gps_point as
(
    gps_id integer,
    gps_street text,
    gps_city text,
    gps_postalcode text,
    gps_type api_enumerator,
    gps_lat double precision,
    gps_lon double precision,
    gps_counter integer,
    gps_label text
);

In fact, it's a big big JSON object that I cut to small part before the database and my last problem is to give it as an list/array

CREATE OR REPLACE FUNCTION v01_api_put_eventsCreatePUT
  (
      car_id            INTEGER
    , date_hour_go      TIMESTAMP
    , date_hour_return  TIMESTAMP
    , daysTable         INTEGER []
    , gps_points        API_GPS_POINT []
    , hours_type_go     API_ENUMERATOR
    , hours_type_return API_ENUMERATOR
    , pref_state        API_ENUMERATOR
    , recurrent         BOOLEAN
    , roundtrip         BOOLEAN
    , subscribable      BOOLEAN
    , time_deviation    TIMESTAMP
    , time_start_avg    TIMESTAMP
    , apikey            TEXT
  )
  RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
  bool BOOLEAN;
  me   UUID;
  ann  ANNOUNCEMENT;
BEGIN


  RAISE NOTICE 'IN v01_api_put_eventsCreatePUT';
  SELECT *
  FROM cheickApiKey
  (
      apikey
  )
  INTO me;

  -- PREPARE ANNOUNCEMENT
  ann.car_id := car_id;
  ann.ann_time_deviation := time_deviation;
  ann.ann_time_start_avg := time_start_avg;
  ann.ann_subscribable := subscribable;
  ann.ann_recurrent := recurrent;

  -- ONE WAY ----------------------
  ann.hours_type_id := hours_type_go.id;

  FOR i IN 1..array_length(daysTable, 1)
  LOOP
    -- CALCUL EXACT DAY
    SELECT *
    FROM
        event_find_next_dayofweek
        (
            date_hour_go
            , daysTable [i]
        )
    INTO
      ann.ann_dh;

    -- CREATE ONE EVENT
    SELECT *
    FROM event_create
    (
        me
        , gps_points
        , ann
        , pref_state
    )
    INTO bool;

  END LOOP...........

Best Answer

Confusion?

From the docs json_populate_recordset(base anyelement, from_json json) does not return an ARRAY, it returns a setof anyelement. That's fundamentally different from an array.

SELECT * FROM (SELECT ARRAY[1,2]) AS gs(x);   -- array (int[])
SELECT * FROM (VALUES (1), (2)  ) AS gs(x);   -- setof anyelement (setof int)

I am of the opinion that you do not need an array, nor a setof anyelement.

What you need to do things your way

For comparison this will turn json into foo[],

CREATE TYPE foo AS ( id int );
SELECT pg_typeof(
  ARRAY(
    SELECT *
    FROM json_populate_recordset( NULL::foo, '[{"id":1},{"id":2}]' ) )
  )
);

Which you can call like this

SELECT test_gps(
  ARRAY(
    SELECT *
    FROM json_populate_recordset( NULL::foo, '[{"id":1},{"id":2}]' ) )
  )
);

A better way!

But the better way is to simply not process the whole result set but only a single json value.

CREATE OR REPLACE FUNCTION test_gps
  (
    gps_points API_GPS_POINT
  )
  RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
  api_gps_points API_GPS_POINT;
BEGIN

    RAISE NOTICE 'API_GPS_POINT : %', API_GPS_POINT;

END;
$$;

And then call it like this..

SELECT test_gps(myjson)
FROM json_populate_recordset( null::foo, '[{"id":1},{"id":2}]' )
  AS myjson;

Which you can see returns one type of foo.

SELECT pg_typeof(myjson)
FROM json_populate_recordset( null::foo, '[{"id":1},{"id":2}]' )
  AS myjson;
Related Question