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...........
Improve this question
0

1 Answer 1

Reset to default
4

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;
Improve this answer
5
  • 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. Commented Aug 2, 2017 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. Commented Aug 2, 2017 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 Commented Aug 3, 2017 at 9:43
  • I don't believe you. I don't think you know what you're doing. Commented Aug 3, 2017 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/… Commented Aug 3, 2017 at 16:25

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.