Re: Optimizing query

On 2026-09-09 18:22:52 -0400, Ron Johnson wrote:
> On Wed, Sep 9, 2026 at 1:43 PM Igor Korot <ikorot01(at)gmail(dot)com> wrote:
> Can below 4 queries
>
>     std::wstring query1 = L"SELECT rolname FROM pg_roles";
>     std::wstring query2 = L"SELECT datname FROM pg_database WHERE
> datistemplate = true;";
>     std::wstring query3 = L"SELECT pg_encoding_to_char( conforencoding
> ) AS name FROM pg_conversion";
>     std::wstring query4 = L"SELECT collname, collencoding,
> collprovider collctype FROM pg_collation";
>     std::wstring query5 = L"SELECT spcname FROM pg_tablespace";
>
> be made as one big query to use a 1 DB hit?
>
>
> Bereft of the C++ cruft, here are the queries:
> SELECT rolname
> FROM pg_roles;
>
> SELECT datname
> FROM pg_database
> WHERE datistemplate = true;
>
> SELECT pg_encoding_to_char(conforencoding) AS name
> FROM pg_conversion;
>
> SELECT collname, collencoding, collprovider, collctype
> FROM pg_collation;
>
> SELECT spcname
> FROM pg_tablespace;
>
> The sticky wicket is the four columns in the pg_collation query.
>
> You could probably write a stored function or procedure which returns a complex
> json object with the results of the four queries (or four jsonb objects, one
> for each query).

You don't need a stored procedure for that. Simple (although a bit
verbose) SQL is sufficient.

I'm just using two of the queries here for brevity, since the other two
have same structure as the pg_roles query.

> Without the pg_collation query, you could make a UNION ALL like:

You can still do this:

SELECT 'pg_roles' as "table", array_to_json(array_agg(rolname)) as "info"
FROM pg_roles
union all
SELECT 'pg_collation',
array_to_json(array_agg(json_build_object(
'collname', collname,
'collencoding', collencoding,
'collprovider', collprovider,
'collctype', collctype
)))
FROM pg_collation;

or you could use CTEs and a join to put them into columns of a single
row:

with pg_roles as (
SELECT array_to_json(array_agg(rolname)) as pg_roles
FROM pg_roles
),
pg_collation as (
SELECT
array_to_json(array_agg(json_build_object(
'collname', collname,
'collencoding', collencoding,
'collprovider', collprovider,
'collctype', collctype
))) as pg_collation
from pg_collation
)
select * from pg_roles, pg_collation;

Personally I would stick with the individual queries. I don't see much
value in packing all of that into a single query.

hjp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"