1

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...........
2

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;
  • Thank for your answer. Yes i don't see the difference between ARRAY and setoff any element. I prefer process the data from nodeJS for prepare a little the query outside, and transmit only real sql type So the best option for me, (look on my edit post) is to try your first proposition, I will check tomorrow... thank. – Vincent Vost Aug 2 '17 at 21:52
  • "Yes i don't see the difference between ARRAY and setoff any element." An array is a data type. A setof anyelement, is typed resultset. I show an example of both in my answer. – Evan Carroll Aug 2 '17 at 22:42
  • Ok, I cannot achieve what I need for the moment. Method Array can only return one column. What I would like to do is to pass on parameter a SET OF as the result return by json_populate_recordset. So which kind of variable I need in my fonction for receive the result? Thank – Vincent Vost Aug 3 '17 at 9:43
  • I don't believe you. I don't think you know what you're doing. – Evan Carroll Aug 3 '17 at 15:57
  • You are right, I'm not sure how to do it. I create a new question, I hope it's more clear, dba.stackexchange.com/questions/182615/… – Vincent Vost Aug 3 '17 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.