Postgresql – Reconstructing row from record

postgresql

Based on Northwind schema:

Assume we have a query like this (please disregard the actual usefulness of this, as the real use case is more involved)

SELECT unnest(r.agg) FROM (
  SELECT array_agg(x) AS agg
  FROM (
     SELECT *
     FROM northwind.u_suppliers s LEFT JOIN northwind.u_products p ON s.guid = p.supplier
     ) x
  GROUP BY x.supplier
  ) r LIMIT 1;

the result is 

(9Y_03ogA1mOixVtJTqB8zih9Lu5-DB-l--fYvnHhyMQ,22,"Dirk Luchte","Accounting Manager",6j7qZ3Pjad5irKAM1MKX1WU7hvkgIOhZoe9OeRfTX4Q,"(12345) 1210",,northwindSupplier,jp9VvzFBbklHelD_ybS1oDsJbILda1duNhMtCdGBZqg,0,do4_k8zCsOog5tYROlsouF81Uj9I5LbTFawvuPw9Q-0,"2017-10-11 08:45:14.982",47,"Zaanse koeken",9Y_03ogA1mOixVtJTqB8zih9Lu5-DB-l--fYvnHhyMQ,AkOUoKYOpHFWIkNcoPX3CLtTl4n_ACN6TwWxqd3gzYQ,"10 - 4 oz boxes",9.5,36,0,0,f,,northwindProduct)

which is a record.

What would be the way to create a table from this with all columns and unnested records as rows?

So there'd be:

u_suppliers_column1 | ... | u_suppliers.columnN | u_products.column1 | ... | u_products.columnN
------------------------------------------------------------------------------------------------
9Y_03og...          | 22 | "Dirk Luchte" ...

The snippet is for illustrational purpose only – of course, creating aggregate just to unnest it again is non-sensible under these circumstances, but for sake of simplicity & reproducibility I have decided to write it as such,.

Best Answer

Thanks to @ypercubeᵀᴹ , one can go about it by first creating intermediary type with all the required columns (from both tables, since it's join) such as:

CREATE TYPE suppliers_products AS (s_guid VARCHAR(64), supplierId, ...)

The following query can be then written such as:

SELECT (unnest(r.agg)).*
  FROM (
     SELECT array_agg(x :: suppliers_products) AS agg
     FROM (
            SELECT *
            FROM northwind.u_suppliers S LEFT JOIN northwind.u_products p ON S.guid = p.supplier
          ) x
     GROUP BY x.supplier
   ) r
  LIMIT 1;

If there was no anonymous record (coming from join), we would not need intermediate type.

As far as I'm aware there is no way to do it without manually preparing such type upfront.

Related Question