docs: how to get the last sample records in one step

[cobolbaby]

How to quickly retrieve the latest sampling records? Currently, my approach is to use a query similar to the following:

select max(time) from dm.iot_sqt_agv_count where time > now() - interval '1 hour';
select * from dm.iot_sqt_agv_count where time = v_max_time;

How to use the last function in the README?

[MinhLA1410]

Hi @cobolbaby ,

How to use the last function in the README?

Last function has 3 ways to use follow https://docs.influxdata.com/influxdb/v1/query_language/functions/#last.
We also provide 3 this ways on influxdb_fdw:

• LAST (field_key) = last(timestamp with time zone, anyelement) : Returns the newest field value (determined by timestamp) associated with the field key.
  select last(time, field) from dm.iot_sqt_agv_count;
  We need to specify a time column to can pushdown this function to influxdb server
  Please refer Here
• LAST () = last_all() : Returns the newest field value (determined by timestamp) associated with each field key in the measurement.
  select last_all(*) from dm.iot_sqt_agv_count;
  Please refer Here
• LAST() = last(text): Returns the newest field value (determined by timestamp) associated with each field key that matches the regular expression.
  select last('/value[1,4]/') from dm.iot_sqt_agv_count;
  Please refer Here

[cobolbaby]

The default UDFs might not meet the requirements; they either fail to retrieve results or return text data that looks like tuples. Could you give me some suggestions?

bdc=# \d dm.iot_sqt_agv_count;
                        Foreign table "dm.iot_sqt_agv_count"
  Column  |           Type           | Collation | Nullable | Default | FDW options 
----------+--------------------------+-----------+----------+---------+-------------
 time     | timestamp with time zone |           |          |         | 
 agvState | character varying        |           |          |         | 
 value    | numeric                  |           |          |         | 
Server: influxdb_iot
FDW options: ("table" 'F3AGV.count', tags 'agvState,value', schemaless 'true')

bdc=# select * from dm.iot_sqt_agv_count order by time desc limit 10;
            time            |  agvState  | value 
----------------------------+------------+-------
 2024-05-20 18:15:55.562+08 | onlineAGV  |    88
 2024-05-20 18:15:55.562+08 | LOCKED     |    11
 2024-05-20 18:15:55.562+08 | IDLE       |     5
 2024-05-20 18:15:55.562+08 | offlineAGV |     3
 2024-05-20 18:15:55.562+08 | BUSY       |    70
 2024-05-20 18:15:55.562+08 | totalAGV   |    91
 2024-05-20 18:15:55.562+08 | CHARGING   |     1
 2024-05-20 18:15:55.562+08 | ERROR      |     1
 2024-05-20 18:14:55.572+08 | totalAGV   |    91
 2024-05-20 18:14:55.572+08 | BUSY       |    71
(10 rows)

bdc=# select last(time, "agvState") from dm.iot_sqt_agv_count;
 last 
------
(0 rows)

bdc=# select last(time, value) from dm.iot_sqt_agv_count;
ERROR:  stub last_sfunc(anyelement, timestamp with time zone, anyelement) is called
CONTEXT:  PL/pgSQL function last_sfunc(anyelement,timestamp with time zone,anyelement) line 3 at RAISE

bdc=# select last_all(*) from dm.iot_sqt_agv_count;
                      last_all                       
-----------------------------------------------------
 (2024-05-20T10:16:55.548Z,,"{\"value\" : \"91\" }")
(1 row)

bdc=# select last('/value/') from dm.iot_sqt_agv_count;
                        last                         
-----------------------------------------------------
 (2024-05-20T10:16:55.548Z,,"{\"value\" : \"91\" }")
(1 row)

bdc=# select max(time) from dm.iot_sqt_agv_count where time > now() - interval '1 hour';
            max             
----------------------------
 2024-05-20 18:17:55.564+08
(1 row)

bdc=# select * from dm.iot_sqt_agv_count where time = '2024-05-20 18:17:55.564+08';
            time            |  agvState  | value 
----------------------------+------------+-------
 2024-05-20 18:17:55.564+08 | BUSY       |    70
 2024-05-20 18:17:55.564+08 | CHARGING   |     1
 2024-05-20 18:17:55.564+08 | ERROR      |     1
 2024-05-20 18:17:55.564+08 | IDLE       |     7
 2024-05-20 18:17:55.564+08 | LOCKED     |    10
 2024-05-20 18:17:55.564+08 | offlineAGV |     2
 2024-05-20 18:17:55.564+08 | onlineAGV  |    89
 2024-05-20 18:17:55.564+08 | totalAGV   |    91
(8 rows)

[MinhLA1410]

Hello @cobolbaby
Sorry for late response.

Could you give me some suggestions?

The last() function cannot work because the last() function only works for key fields. https://docs.influxdata.com/influxdb/v1/query_language/functions/#last
As your FDW options FDW options: ("table" 'F3AGV.count', tags 'agvState,value', schemaless 'true')
avgState and value column is the tag fields, so the error occurs.

[cobolbaby]

How to convert the text (2024-05-20T10:16:55.548Z,,"{\"value\" : \"91\" }") like tuples to columns?

[MinhLA1410]

How to convert the text (2024-05-20T10:16:55.548Z,,"{"value" : "91" }") like tuples to columns?

Can you try select (last_all(*)::dm.iot_sqt_agv_count).* from dm.iot_sqt_agv_count ?

[cobolbaby]

select (last_all()::dm.iot_sqt_agv_count). from dm.iot_sqt_agv_count

ERROR: invalid input syntax for type numeric: "{"value" : "91" }"

SQL state: 22P02

[MinhLA1410]

ERROR: invalid input syntax for type numeric: "{"value" : "91" }"

Thank you for feedback. But I would like to know your table schema is correct?

bdc=# \d dm.iot_sqt_agv_count;
                        Foreign table "dm.iot_sqt_agv_count"
  Column  |           Type           | Collation | Nullable | Default | FDW options 
----------+--------------------------+-----------+----------+---------+-------------
 time     | timestamp with time zone |           |          |         | 
 agvState | character varying        |           |          |         | 
 value    | numeric                  |           |          |         | 
Server: influxdb_iot
FDW options: ("table" 'F3AGV.count', tags 'agvState,value', schemaless 'true')

The schemaless mode is enabled. But your column is agvState and value. I don't understand why because Columns of foreign table in schemaless mode are fixed with names and types as below: (time timestamp with time zone, tags jsonb, fields jsonb)

[cobolbaby]

The schemaless mode is enabled. But your column is agvState and value.

You're right, I didn't notice the option. It seems there were no errors with schemaless 'true' before. I'll alter the option and test it again.

ALTER FOREIGN TABLE IF EXISTS dm.iot_sqt_agv_count
    OPTIONS (SET schemaless 'false');

select (last_all(*)::dm.iot_sqt_agv_count).* from dm.iot_sqt_agv_count;

ERROR:  Too many columns.malformed record literal: "(2024-08-05T04:38:22.974Z,,,)" 

ERROR:  malformed record literal: "(2024-08-05T04:38:22.974Z,,,)"
SQL state: 22P02
Detail: Too many columns.

select * from dm.iot_sqt_agv_count order by time desc limit 10;

"2024-08-05 12:46:23.022+08"	"onlineAGV"	88
"2024-08-05 12:46:23.022+08"	"LOCKED"	12
"2024-08-05 12:46:23.022+08"	"IDLE"	18
"2024-08-05 12:46:23.022+08"	"offlineAGV"	3
"2024-08-05 12:46:23.022+08"	"BUSY"	52
"2024-08-05 12:46:23.022+08"	"totalAGV"	91
"2024-08-05 12:46:23.022+08"	"CHARGING"	6
"2024-08-05 12:46:23.022+08"	"ERROR"	0
"2024-08-05 12:45:22.997+08"	"totalAGV"	91
"2024-08-05 12:45:22.997+08"	"BUSY"	50

[MinhLA1410]

"(2024-08-05T04:38:22.974Z,,,)"

It looks like your remote table (table on Influxdb) has more columns than 3 (time, avgState, value).
Because the last_all(*) function is pushed down to influxdb to get the results directly from influxdb. The returned result includes the actual column number but is cast according to the number columns of foreign table so the Too many columns.malformed record literal error is thrown.

It looks like your remote table (table on Influxdb) has more columns than 3 (time, avgState, value).

is it correct?

[cobolbaby]

It looks like your remote table (table on Influxdb) has more columns than 3 (time, avgState, value).

Yes.

[MinhLA1410]

@cobolbaby

Yes

How to convert the text (2024-05-20T10:16:55.548Z,,"{"value" : "91" }") like tuples to columns?

To expose it, it must be able to cast the record to a table data type where the data type matches the value (including number of columns, and data type of each column).
There are 2 ways:

• Re-create foreign table schema to match with remote table
• Define a new type which matches with the record and then cast it to that type.

create type record_type as (time timestamp, avgState varchar, value numeric, column_x int, column_y text, ....)
select (last_all(*)::record_type ).* from dm.iot_sqt_agv_count;

[mkgrgis]

@cobolbaby , is the problem resolved after last release?