TableOID in description of inlined function

From: Marius Lorek <marius(dot)lorek(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: TableOID in description of inlined function
Date: 2024-02-01 09:22:35
Message-ID: CAAajOOkyBWi2GkPx=e+=Oms+P47r4nv1WH9ZcGxTQ6uk1CXvEw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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:

explain select * from my_function();
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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Allan Kamau 2024-02-01 10:50:20 Re: building a singularity image from docker hub postgres image
Previous Message Michael Paquier 2024-02-01 08:46:36 Re: support fix query_id for temp table