Hi, On Wed, Sep 9, 2026 at 5:23 PM Ron Johnson <[email protected]> wrote: > > On Wed, Sep 9, 2026 at 1:43 PM Igor Korot <[email protected]> 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!
