Re: MERGE and parsing with prepared statements - Mailing list pgsql-hackers

From Justin Pryzby
Subject Re: MERGE and parsing with prepared statements
Date
Msg-id 20220715175854.GM18011@telsasoft.com
Whole thread Raw
In response to Re: MERGE and parsing with prepared statements  (Matthias van de Meent <boekewurm+postgres@gmail.com>)
Responses Re: MERGE and parsing with prepared statements
List pgsql-hackers
On Fri, Jul 15, 2022 at 11:25:35AM +0200, Matthias van de Meent wrote:
> On Thu, 14 Jul 2022, 18:26 Justin Pryzby, <pryzby@telsasoft.com> wrote:
> >
> > Why is $1 construed to be of type text ?
> 
> The select statement that generates the row type of T `(select $1 CID,
> $2 TxV) AS T` does not put type bounds on the input parameters, so it
> remains `unknown` for the scope of that subselect. Once stored into
> the row type, the type of that column defaults to text, as a row type
> should not have 'unknown'-typed columns.

Thanks for looking into it.

I see now that the same thing can happen with "ON CONFLICT" if used with a
subselect.

PREPARE p AS INSERT INTO t SELECT a FROM (SELECT $1 AS a)a
     ON CONFLICT (i) DO UPDATE SET i=excluded.i;
ERROR:  column "i" is of type integer but expression is of type text

It seems a bit odd that it's impossible to use merge with prepared statements
without specifically casting the source types (which I did now to continue my
experiment).

-- 
Justin



pgsql-hackers by date:

Previous
From: Jacob Champion
Date:
Subject: Re: Transparent column encryption
Next
From: Maciek Sakrejda
Date:
Subject: Re: Reduce timing overhead of EXPLAIN ANALYZE using rdtsc?