Hi All,
If I have a table
create table my_table(id int primary key);
and a function
create function my_function() returns table(id int) stable as $$ select * from my_table $$ language sql;
then Postgres knows that selecting from the function is really just selecting from the table:
But if you prepare the same select statement and ask Postgres for a description of it, then in the response the column "id" will have a TableOID of 0 - even though we know, on some level, that it's going to be selected straight from a table. I started looking at this because sqlc (https://github.com/sqlc-dev/sqlc) uses TableOID to infer the nullability of the column.
Can someone explain how these two things are connected (or aren't)? Perhaps more importantly, is there a workaround?
If I have a table
create table my_table(id int primary key);
and a function
create function my_function() returns table(id int) stable as $$ select * from my_table $$ language sql;
then Postgres knows that selecting from the function is really just selecting from the table:
explain select * from my_function();
QUERY PLAN
------------------------------------------------------------
Seq Scan on my_table (cost=0.00..35.50 rows=2550 width=4)
(1 row)
QUERY PLAN
------------------------------------------------------------
Seq Scan on my_table (cost=0.00..35.50 rows=2550 width=4)
(1 row)
But if you prepare the same select statement and ask Postgres for a description of it, then in the response the column "id" will have a TableOID of 0 - even though we know, on some level, that it's going to be selected straight from a table. I started looking at this because sqlc (https://github.com/sqlc-dev/sqlc) uses TableOID to infer the nullability of the column.
Can someone explain how these two things are connected (or aren't)? Perhaps more importantly, is there a workaround?
