| 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: | Whole Thread | Raw Message | 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. |