Search code examples
sqldatabasepostgresqlnpgsql

PostgreSQL - best way to return an array of key-value pairs

I'm trying to select a number of fields, one of which needs to be an array with each element of the array containing two values. Each array item needs to contain a name (character varying) and an ID (numeric). I know how to return an array of single values (using the ARRAY keyword) but I'm unsure of how to return an array of an object which in itself contains two values.

The query is something like

SELECT
    t.field1,
    t.field2,
    ARRAY(--with each element containing two values i.e. {'TheName', 1 })
FROM MyTable t

I read that one way to do this is by selecting the values into a type and then creating an array of that type. Problem is, the rest of the function is already returning a type (which means I would then have nested types - is that OK? If so, how would you read this data back in application code - i.e. with a .Net data provider like NPGSQL?)

Any help is much appreciated.

Solution

  • I suspect that without having more knowledge of your application I'm not going to be able to get you all the way to the result you need. But we can get pretty far. For starters, there is the ROW function:

    # SELECT 'foo', ROW(3, 'Bob');
 ?column? |   row   
----------+---------
 foo      | (3,Bob)
(1 row)

    So that right there lets you bundle a whole row into a cell. You could also make things more explicit by making a type for it:

    # CREATE TYPE person(id INTEGER, name VARCHAR);
CREATE TYPE
# SELECT now(), row(3, 'Bob')::person;
              now              |   row   
-------------------------------+---------
 2012-02-03 10:46:13.279512-07 | (3,Bob)
(1 row)

    Incidentally, whenever you make a table, PostgreSQL makes a type of the same name, so if you already have a table like this you also have a type. For example:

    # DROP TYPE person;
DROP TYPE

# CREATE TABLE people (id SERIAL, name VARCHAR);
NOTICE:  CREATE TABLE will create implicit sequence "people_id_seq" for serial column "people.id"
CREATE TABLE

# SELECT 'foo', row(3, 'Bob')::people;
 ?column? |   row   
----------+---------
 foo      | (3,Bob)
(1 row)

    See in the third query there I used people just like a type.

    Now this is not likely to be as much help as you'd think for two reasons:

    1. I can't find any convenient syntax for pulling data out of the nested row.

      I may be missing something, but I just don't see many people using this syntax. The only example I see in the documentation is a function taking a row value as an argument and doing something with it. I don't see an example of pulling the row out of the cell and querying against parts of it. It seems like you can package the data up this way, but it's hard to deconstruct after that. You'll wind up having to make a lot of stored procedures.

    2. Your language's PostgreSQL driver may not be able to handle row-valued data nested in a row.

      I can't speak for NPGSQL, but since this is a very PostgreSQL-specific feature you're not going to find support for it in libraries that support other databases. For example, Hibernate isn't going to be able to handle fetching an object stored as a cell value in a row. I'm not even sure the JDBC would be able to give Hibernate the information usefully, so the problem could go quite deep.

    So, what you're doing here is feasible provided you can live without a lot of the niceties. I would recommend against pursuing it though, because it's going to be an uphill battle the whole way, unless I'm really misinformed.