Re: BUG #15940: json_populate_recordset fails with ERROR: record type has not been registered

On Tue, Aug 6, 2019 at 2:38 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Dmitry Dolgov <9erthalion6(at)gmail(dot)com> writes:
> > On Tue, Aug 6, 2019 at 9:32 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> >> On Tue, Aug 06, 2019 at 06:41:34AM +0000, PG Bug reporting form wrote:
> >>> Following query works fine in previous freebsd versions
> >>>
> >>> SELECT
> >>> id_item
> >>> FROM json_populate_recordset(null::record, '[{"id_item":776}]')
> >>> AS
> >>> (
> >>> id_item int
> >>> );
>
> > Looks like there are tests for such behaviour with exactly this error,
> > is there a chance it was a feature?
>
> Yeah, my first reaction to this bug report was "didn't we fix this
> already?" --- it's real close to some other behaviors we fixed in
> that code.
>
> In an ideal world we'd decide that this query is wrong and we should
> not support it. The point of json_populate_recordset is to take the
> result rowtype from its first argument; if you want to take the result
> rowtype from the call context, you should be using json_to_recordset.
> I really don't like semantics as squishy as "we'll take the result
> type from the first argument except if it's exactly a null of type
> RECORD, and then we'll look somewhere else for the type". Yeah, it
> worked that way before v11, but IMO that was a bug.
>
> Now, it's surely true that if we are going to take that attitude we
> ought to throw some better error about it than "record type has not been
> registered"; that's my fault for not thinking harder about the UX.
> (I think that there are some related cases where < v11 already
> threw that error, and it seemed sufficient to keep on doing so.)

still does.

postgres=# select version();
version
─────────────────────────────────────────────────────────────────────────────────────────────────────────
PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)

postgres=# select (row(1,2,3)).*;
ERROR: record type has not been registered

For posterity I agree that OP was essentially exploiting undefined, or
at least poorly defined, behavior. For my money, I'd advise using
this function for cases where you don't want to use an in place type,
just a column list:

postgres=# SELECT * from json_to_recordset('[{"id_item":776}]') as
(id_item int);
id_item
─────────
776

merlin