0

I have constructed a naive query that works for my application, which returns multiple values from a table that contains an array of reals.

SELECT 
  "timestamp" AS "time",
  type as metric,
  id,
  values[80],
  values[81],
  values[82],
  values[83],
  values[84],
  values[85],
  values[86],
  values[87],
  values[88],
  values[89],
  values[91],
  values[92],
  values[93],
  values[94],
  values[95],
  values[96],
  values[97],
  values[98],
  values[99],
  values[100],
  values[101]

FROM test_table
WHERE
  "timestamp" BETWEEN '2021-04-03T15:05:56.928Z' AND '2021-04-03T15:10:56.928Z'
ORDER BY 1

However, for obvious reasons, I want to avoid long lists of values[n] in my query, especially as in fact I need to get up to 1000 values.

I know that it is possible to iterate over an array but everything I try to replace the list of array subscripts along the lines of

  FOR i IN ARRAY values
  LOOP 
      values [i]
  END LOOP;

errors out.

As an obvious SQL noob, can someone suggest how this might query might be done more elegantly?

Jonathan Peace
  • 1
  • 1
  • Why not just return the array? – Gordon Linoff Apr 04 '21 at 02:01
  • because the result is fed as bucketed timeseries data into Grafana which expects a set of values – Jonathan Peace Apr 04 '21 at 03:00
  • 1
    You don't show a `set of values`. You show a row with many columns. What result do you need *exactly*? – Erwin Brandstetter Apr 04 '21 at 03:38
  • I can imagine doing this in multiple steps: - turn the single row that you want to get from test_table into a table, expanding the arrai values into one value per row in this new table, using generate_subscripts(): https://www.postgresql.org/docs/9.1/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS - use a pivot to turn this table back into a single row, using crosstab(): https://stackoverflow.com/questions/3002499/postgresql-crosstab-query/11751905#11751905 – moilejter Apr 04 '21 at 04:14
  • @moilejter this sounds a bit beyond my very basic SQL skills, but I will have a go, thanks! – Jonathan Peace Apr 04 '21 at 05:14
  • @ErwinBrandstetter, the result that I need is a row with n (eg512) columns, because that is what Grafana expects in order to display bucketed data. More precisely. I am trying to produce a waterfall/ heatmap plot of of an FFT result, which is stored as an array of n bins, and Grafana needs a timeseries with n columns to display a single Y bucket; passing an array does not work alas. - this works as shown above, but is clumsy so I was trying to find a better way, thanks – Jonathan Peace Apr 04 '21 at 05:15
  • Follow up here if you need more help ... I have to confess that I haven't used postgresql myself - so it would be a learning experience for both of us :-). But maybe someone else will chime in :-p – moilejter Apr 04 '21 at 06:12
  • If you need a 100 columns, you need to write a 100 expressions one way or another. There is no way around that. – a_horse_with_no_name Apr 04 '21 at 06:31

0 Answers0