Hi,
On 2019/02/28 10:45, Michael Paquier wrote:
> On Wed, Feb 27, 2019 at 03:48:08PM -0300, Alvaro Herrera wrote:
>> I just happened to come across the result of this rationale in
>> pg_partition_tree() (an SRF) while developing a new related function,
>> pg_partition_ancestors(), and find the resulting behavior rather absurd
>> -- it returns one row with all NULL columns, rather than no rows. I
>> think the sensible behavior would be to do SRF_RETURN_DONE() before
>> stashing any rows to the output, so that we get an empty result set
>> instead.
>
> Hmm. Going through the thread again NULL was decided to make the
> whole experience consistent, now by returning nothing we would get
> a behavior as consistent as when NULL is used in input, so point taken
> to tune the behavior for unsupported relkinds and undefined objects.
>
> Does the attached look fine to you?
Reading the discussion, we just don't want to throw an "ERROR: unsupported
relkind" error, to avoid, for example, aborting a query that's iteratively
calling pg_partition_tree on all relations in a given set. Returning no
rows at all seems like a better way of ignoring such relations.
with rels as (select relname from pg_class where relnamespace =
'public'::regnamespace) select pg_partition_tree(relname::regclass) from
rels;
pg_partition_tree
────────────────────────────────────────
(pk1,pk,t,0)
(pk,,f,0)
(pk1,pk,t,1)
(pk1_pkey,pk_pkey,t,0)
(pk_pkey,,f,0)
(pk1_pkey,pk_pkey,t,1)
(another_pk1,another_pk,t,0)
(another_pk,,f,0)
(another_pk1,another_pk,t,1)
(another_pk1_pkey,another_pk_pkey,t,0)
(another_pk_pkey,,f,0)
(another_pk1_pkey,another_pk_pkey,t,1)
(13 rows)
Now that Alvaro mentions it, I too think that returning no rows at all is
better than 1 row filled with all NULLs, so +1.
Thanks,
Amit