Re: Optimizing query

Thx.

On Wed, Sep 9, 2026 at 3:44 PM Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:

> On Wed, Sep 9, 2026 at 6:33 PM Igor Korot <ikorot01(at)gmail(dot)com> wrote:
>
>> 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.
>>
>
> I could probably FULL OUTER JOIN them on the oid column. I don't know how
> many rows are pg_collation, but if there are a lot, you're going to be
> returning a whole lot of rows with null values, and application logic must
> do something like "if *this* column is not null, then it's a role; if
> *that* column is not null, then it's a database; etc etc".
>
> KISS and make four queries...
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>