From: | Dominique Devienne <ddevienne(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Get back the number of columns of a result-set prior to JSON aggregation |
Date: | 2023-11-28 12:12:20 |
Message-ID: | CAFCRh-8tyng7SzUea8KoNsOgSf667un1GN3rJTsP5xXT_0tDug@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi. I've got a nice little POC using PostgreSQL to implement a REST API
server.
This uses json_agg(t) to generate the JSON of tables (or subqueries in
general),
which means I always get back a single row (and column, before I added the
count(t.*)).
But I'd like to get statistics on the number of rows aggregated (easy,
count(*)),
but also the number of columns of those rows! And I'm stuck for the
latter...
Is there a (hopefully efficient) way to get back the cardinality of a
select-clause basically?
Obviously programmatically I can get the row and column count from the
result-set,
but I see the result of json_agg() myself, while I want the value prior to
json_agg().
Is there a way to achieve this?
Thanks, --DD
PS: In the example below, would return 1 for the 1st query, and 2 for the
2nd.
```
migrated=> create table t1 (id integer);
CREATE TABLE
migrated=> insert into t1 values (1), (2);
INSERT 0 2
migrated=> create table t2 (id integer, name text);
CREATE TABLE
migrated=> insert into t2 values (1, 'one'), (2, 'two');
INSERT 0 2
migrated=> select count(t.*), json_agg(t) from t1 t;
count | json_agg
-------+-------------
2 | [{"id":1}, +
| {"id":2}]
(1 row)
migrated=> select count(t.*), json_agg(t) from t2 t;
count | json_agg
-------+--------------------------
2 | [{"id":1,"name":"one"}, +
| {"id":2,"name":"two"}]
(1 row)
```
From | Date | Subject | |
---|---|---|---|
Next Message | hector vass | 2023-11-28 14:14:05 | Re: How to eliminate extra "NOT EXISTS"-query here? |
Previous Message | Kyotaro Horiguchi | 2023-11-28 08:54:43 | Re: Could not read from file "pg_subtrans/00F5" at offset 122880: Success. |