Re: Optimizing query

On Wed, Sep 9, 2026 at 1:43 PM Igor Korot <ikorot01(at)gmail(dot)com> wrote:

> Hi, ALL,
> 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'd of course have to decode the json in
your C++ program.

Without the pg_collation query, you could make a UNION ALL like:
SELECT 'pg_roles' AS table_name, rolname::text AS row_value
FROM pg_roles
UNION ALL
SELECT 'pg_database' AS table_name, datname ::text AS row_value
FROM pg_database
WHERE datistemplate = true
UNION ALL
SELECT ' pg_conversion' AS table_name
, pg_encoding_to_char(conforencoding)::text AS row_value
FROM pg_conversion
UNION ALL
SELECT pg_tablespace AS table_name, spcname ::text AS row_value
FROM pg_tablespace;

It's a lot of complication, though, for something which shouldn't be called
very often.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!