Re: Optimizing query

Hi,

On Wed, Sep 9, 2026 at 5:23 PM Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:
>
> 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.

I was hoping to have some kind of join query. ;-)
But you are right - the UNION ALL doesn't make sense for a
"1-in-a-lifetime" query.

Thank you.

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