Thx.
On Wed, Sep 9, 2026 at 3:44 PM Ron Johnson <[email protected]> wrote: > On Wed, Sep 9, 2026 at 6:33 PM Igor Korot <[email protected]> wrote: > >> 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. >> > > 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! >
