0

What I need is a string/text json-compliant array of arrays like the following:

[
    [1421420100, 14214201003487],
    [1421420101, 14214201003487],
    [1421420109, 14214201003487]
...
]

in order to return it from the server to the client without any further elaboration.

I have a function that queries on a table that returns the two fields, but I would like to return an array of arrays in order to call the function and then call array_to_json. Then, I would like to convert it into text/string.

Is that possible? Can an array of arrays have two different types?

Bertuz
  • 2,390
  • 3
  • 25
  • 50
  • why dont use array of objects? [{type1,type2}] – AliBoronsi Nov 18 '16 at 15:27
  • because the expected output is what I posted, but in case is not possibile I'll do that way – Bertuz Nov 18 '16 at 15:30
  • Can an array of arrays have two different types? yse, it can – AliBoronsi Nov 18 '16 at 15:35
  • can you please show me the way? I was sure that each element should have the same type. Allowed: `{{1,4}, {5,9}}`, not allowed `{{1,'4'}, {5,'9'}}`. Maybe I'm wrong? According to [this question](http://stackoverflow.com/questions/8167828/multiple-data-types-array-in-postgresql) no – Bertuz Nov 18 '16 at 15:41
  • 1
    @Ali7091: A Postgres array can only include elements of the same type. – Erwin Brandstetter Nov 21 '16 at 17:42

1 Answers1

1

Postgres arrays cannot hold elements of different type.
But you can return an array of composite type (bigint, double precision) instead. The type needs to be registered in the database instance. Either with an explicit CREATE TYPE statement, or implicitly as row type of a table or view.

Example:

CREATE TYPE int8_float AS (b bigint, f float);

CREATE OR REPLACE FUNCTION f_combo_arr()
  RETURNS int8_float[] 
  LANGUAGE sql AS
$func$
   SELECT ARRAY(SELECT (int8_col, float_col)::int8_float FROM tbl);
$func$;

Call:

SELECT f_combo_arr();

float is an alias for double precision in Postgres.

There may be a smarter solution, like returning data type json or a set instead of the array.

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • nice "standard" solution! What about returning a TABLE with the two columns instead? Which of the two do you think is more efficient? I was thinking about returning it and then iterate over it with a cursor – Bertuz Nov 22 '16 at 11:28
  • 1
    @Bertuz: I would prefer a table function with two columns. Probably more efficient for big sets. – Erwin Brandstetter Nov 22 '16 at 13:51