1

I know there is the process of SELECT array_agg(f) FROM (SELECT blah FROM stuff) f, which is great in SQL, but when writing functions in PL/pgSQL, is there a shorthand method??

I'm trying to put JSON keys into an array I can use to look at the length. Something like...

v_len := array_length( array_agg(json_object_keys(myjson)), 1);

instead of the long, DECLARE a variable, do a SELECT array_agg(f) INTO ..., which I've been doing. I've seem hundreds of implementations using the same SQL string, but I really want to cut down my code, my fingers are going numb from all the redundant typing.

What am I missing with that shorthand method?

Guy Park
  • 959
  • 12
  • 25

1 Answers1

5

you can use a ARRAY(subselect) constructor

DO $$
DECLARE a int[];
BEGIN
  a := ARRAY(SELECT * FROM generate_series(1,10));
  RAISE NOTICE 'result: %', a;
END;
$$;
Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • 1
    Awesome, knew it was something short and handy. I ended up using you solution like so... `array_length( ARRAY( SELECT rs FROM json_object_keys(myjson) rs ), 1)`, which works beautifully to get the number of JSON keys. – Guy Park Aug 15 '17 at 10:45
  • 2
    @GuyPark: You can shorten `SELECT rs FROM json_object_keys(myjson) rs` to `SELECT json_object_keys(myjson)` – Nick Barnes Aug 15 '17 at 11:52
  • Cheers for that Nick – Guy Park Aug 29 '17 at 07:10