From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Arthur McGibbon <arthur.mcgibbon@gmail.com>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17051: Incorrect params inferred on PREPARE
In-reply-to: 
 <CAKFQuwbp2upso+eP2j6g0WXqLbtMtvFTmjg1gW3h3oNJSBhnaQ@mail.gmail.com>
References: <17051-c20aad26366e11b4@postgresql.org>
 <CAKFQuwZXSkAhp0xvVbFFriYzq00G8D6-YRjpX7NLJuci+rsXRg@mail.gmail.com>
 <CAL2LXQW=GK2itoa4hBurd97q7Axsbpe+xRRm4DoM1txBGw86MA@mail.gmail.com>
 <CAKFQuwbp2upso+eP2j6g0WXqLbtMtvFTmjg1gW3h3oNJSBhnaQ@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Tue, 08 Jun 2021 06:48:32 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1119339.1623170138.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 08 Jun 2021 12:35:38 -0400
Message-ID: <1119340.1623170138@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1119340.1623170138%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, June 8, 2021, Arthur McGibbon <arthur.mcgibbon@gmail.com> wr=
ote:
>> Could you point me to the documentation - I could only find
>> https://www.postgresql.org/docs/current/sql-prepare.html and it doesn't
>> mention how unknown params are handled.

> https://www.postgresql.org/docs/current/typeconv-union-case.html

Yeah, the point here is that the type of the parameter symbol is
guessed in the context of resolving the CASE construct.  There's
no mechanism for applying external knowledge about what that CASE
ought to yield.  While maybe we could do something in this
specific context, examples that are only slightly more complex
would really be quite impossible.  For example,

... SET timestampCol =3D foo(CASE WHEN timestampCol IS NULL THEN $1 ELSE N=
ULL END);

There's no way to tell which foo() function is meant until we've
identified a result type for the CASE, so the function context is
pretty much a blocker.

Roughly speaking, we only get to use one syntactic level of context
to guess the type of a parameter symbol.  Doing better would really
be a research project.

			regards, tom lane