Re: Get back the number of columns of a result-set prior to JSON aggregation - Mailing list pgsql-general

From hector vass
Subject Re: Get back the number of columns of a result-set prior to JSON aggregation
Date
Msg-id CAJJx+iXfN356dL+KgMyPUURBE9jvswM+fpc9JN=C8Mo8-+0TdA@mail.gmail.com
Whole thread Raw
In response to Get back the number of columns of a result-set prior to JSON aggregation  (Dominique Devienne <ddevienne@gmail.com>)
List pgsql-general

I think you are just trying to get the number of columns in the underlying table, no real cost to read the metadata


select count(id), (select count(attrelid) from pg_attribute where attrelid='t1'::regclass and attnum>0) , json_agg(t) from t1 t;

select count(id), (select count(attrelid) from pg_attribute where attrelid='t2'::regclass and attnum>0) , json_agg(t) from t2 t;



Regards

Hector Vass
07773 352559


On Tue, Nov 28, 2023 at 12:12 PM Dominique Devienne <ddevienne@gmail.com> wrote:
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)
```

pgsql-general by date:

Previous
From: CG
Date:
Subject: Off-label use for pg_repack
Next
From: Adrian Klaver
Date:
Subject: Re: Off-label use for pg_repack