RowDescription for a function does not include table OID
Hello, I am working on a meta-programming use-case where I need to scrape some detailed information about the results of a function that "RETURNS TABLE (LIKE physical_table)", which ends up with prorettype = 'physical_table'::regtype. The problem is that for the query "SELECT * FROM my_function()" the RowDescription that is sent back shows 0 for Table OID and Column Index. >From Wireshark: PostgreSQL Type: Row description Length: 219 Field count: 7 Column name: table_id Table OID: 0 Column index: 0 Type OID: 20 Column length: 8 Type modifier: -1 Format: Binary (1) I would expect that the Table OID contains the relation OID of this table, as it would do for a typical statement like "SELECT * FROM my_table". It would seem there is a bug here that is preventing PostgreSQL from connecting the dots. Regards, Maxwell.
Re: RowDescription for a function does not include table OID
On Friday, June 21, 2024 10:48 AM David G. Johnston wrote: >Yes, but the bug is yours. The definition you want is: RETURNS SETOF >physical_table (not tested though) >What you did was produce a one-column table whose column type is a composite >(and whose name is like - what with case-folding of unquoted identifiers). >Since that table doesn't exist anywhere in the catalogs it has no TableOID. SETOF also does not return correct RowDescription data. Table OID and column number are still both 0. Both versions have the exact same pg_proc.prorettype. If I join this onto pg_type, the pg_type.typrelid = 'physical_table'::regclass. Regards, Maxwell
Re: RowDescription for a function does not include table OID
On Friday, June 21, 2024 11:28 AM David G. Johnston wrote: > Interesting, then I suppose it is semantics. There is no table involved - > you are referencing the type of that name, not the table - so no TableOID. > There is no guarantee the row you are holding came from a table - and I'd > interpret the current behavior as conveying that fact. Though the current > wording: "If the field can be identified as a column of a specific table, the > object ID of the table; otherwise zero."; and the observation that at least a > human "can identify" a related column, leads one to reasonably infer the > system should be able to make such an identification as well. This is exactly my point. If the return type of the function is strongly linked (directly in the function schema) to the table according to pg_catalog, the field can obviously be tied to a specific column of that specific table. The RowDescription not having that value filled in is a violation of that promise. > In short, the system doesn't generate the information you need, where you > need it, to tie these pieces together. Modifying existing elements of the > backend protocol is not presently in the cards. >From my perspective this is clearly a bug as there is no way to define a >function in a way that provides enough data to the reader. Regards, Maxwell.
