Horton

Re: MERGE and parsing with prepared statements

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>, pgsql-hackers@postgresql.org
Date: 2022-07-15T18:40:19Z
Lists: pgsql-hackers 
On 2022-Jul-15, Justin Pryzby wrote:

> On Fri, Jul 15, 2022 at 11:25:35AM +0200, Matthias van de Meent wrote:

> Thanks for looking into it.

Definitely!  Thanks, Matthias.

> 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

Right, I didn't think that MERGE was doing anything peculiar in this
respect.

> 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).

I have no comments on this.  Maybe it can be improved, but I don't know
how.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/

Commits

  1. Doc fixes for MERGE statement

  2. MERGE docs adjustments

  3. Link to MVCC docs in MERGE docs

  4. Fix EXPLAIN MERGE output when no tuples are processed

  5. Make EXPLAIN MERGE output format more compact

  6. Add support for MERGE SQL command

  7. Split ExecUpdate and ExecDelete into reusable pieces

  8. Add API of sorts for transition table handling in trigger.c

  9. Represent Lists as expansible arrays, not chains of cons-cells.