Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pP5dE-0006nJ-Hz for pgsql-general@arkaria.postgresql.org; Mon, 06 Feb 2023 17:50:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pP5dD-0006vE-2v for pgsql-general@arkaria.postgresql.org; Mon, 06 Feb 2023 17:50:19 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pP5dC-0006v5-0u for pgsql-general@lists.postgresql.org; Mon, 06 Feb 2023 17:50:18 +0000 Received: from out3-smtp.messagingengine.com ([66.111.4.27]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pP5d9-0008RY-5B for pgsql-general@postgresql.org; Mon, 06 Feb 2023 17:50:17 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.nyi.internal (Postfix) with ESMTP id C48E25C0182; Mon, 6 Feb 2023 12:50:13 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute3.internal (MEProxy); Mon, 06 Feb 2023 12:50:13 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:date:date:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to; s=fm2; t=1675705813; x= 1675792213; bh=MhxLncdq/a10OXv0tq0qKI7LaLYDvScuFSdJO6KWP8o=; b=d zh33Gm7OfBBJ7dIhF1zRbQZupOUbp7c3do4AzOfojyMbgC2EAhUITRzHqik2pSRl VDiq02MP1gCjyDHRhHoyIIpHLnZfIxbWDfKVpARg9NCVh4BZX3llNlHHNYVspRYQ hU6A3D7KNphClevhouHlj8uMAmjoWVFZOHHZNIfOu23kksiPq63HCkDKItXjyc2C efEdm/pUabfFeYnd4Sv99AqU06njz2kvDCVjLoqVYmxS5MTLxBPGAyiHAUF1FbA/ CqjSgQtqjv7CEM6SLyt9zYo4HhwLwExyRY0i3qI/6PPtATpbthTXsMAVMb9+sjDH RoxeyHQv6dLbfXzoT6B+Q== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :date:date:feedback-id:feedback-id:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:sender :subject:subject:to:to:x-me-proxy:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm3; t=1675705813; x=1675792213; bh=M hxLncdq/a10OXv0tq0qKI7LaLYDvScuFSdJO6KWP8o=; b=Mbr9TqR5X0LOSsVob x1P6buBBeXWxHCkQ/EuelEOOhZdv6t0fRcE77uuPFZFCLCcbIg2KbvG0Kykjkubz chmebWlK09oys1C4nPQZ9OiV+4NkpJ+Ktnxe37g/ZMCzF7FoiX7qda0DmsIrhP45 ypfZMdZoSyu2nXwsJq3SxlO2G+QC+Cuh/uj8KVza2REsJFTqZDeEq4SHknowIwgi Bz87nIBX5lc3eN0+FrJM7MLp5zjEPYCvguAQ8uppYpw2DVACVBuVvPascf37VVdV 5Zr9EpPmzVb8x9w06blXhcqtD9w8vYrleD0blp4Z4EF/yzwThVTPd7AKSErb4Ha5 DF35Q== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrudegiedguddtfecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecunecujfgurhepkfffgggfuffvfhfhjggtgfesth ekredttdefjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdr khhlrghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpeelkeegje fhkeetheefudevteehiedvudehkeduleetfedvffeuhedtvdegtdeffeenucffohhmrghi nhepphhoshhtghhrvghsqhhlrdhorhhgnecuvehluhhsthgvrhfuihiivgeptdenucfrrg hrrghmpehmrghilhhfrhhomheprggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdr tghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Mon, 6 Feb 2023 12:50:12 -0500 (EST) Message-ID: Date: Mon, 6 Feb 2023 09:50:12 -0800 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.6.1 Subject: Re: plpgsql: ambiguous column reference in ON CONFLICT clause To: =?UTF-8?Q?Torsten_F=c3=b6rtsch?= , PostgreSQL mailing lists References: Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2/6/23 09:46, Torsten Förtsch wrote: > Is there a way to prevent this from happening? I know I can use the PK > constraint name or rename the OUT variable i. The question is can this > be resolved while keeping the arbiter inference and the variable name. > > CREATE TABLE x.x ( >     i INT PRIMARY KEY > ); > > CREATE OR REPLACE FUNCTION x.ins(p_i INT, OUT i INT) > LANGUAGE plpgsql AS $$ >   BEGIN >     INSERT INTO x.x(i) >     SELECT p_i >         ON CONFLICT (i) DO NOTHING; >   END > $$; > > postgres=# select * from x.ins(1); > ERROR:  column reference "i" is ambiguous > LINE 3:         ON CONFLICT (i) DO NOTHING >                             ^ > DETAIL:  It could refer to either a PL/pgSQL variable or a table column. > QUERY:  INSERT INTO x.x(i) >     SELECT p_i >         ON CONFLICT (i) DO NOTHING > CONTEXT:  PL/pgSQL function x.ins(integer) line 3 at SQL statement > > The conflicting variable is the OUT parameter of the function. > > Normally, I'd suggest to fully qualify the name but the following or > similar is a syntax error: > >     INSERT INTO x.x(i) AS t >     SELECT p_i >         ON CONFLICT (t.i) DO NOTHING; > > According to the documentation in > https://www.postgresql.org/docs/current/plpgsql-implementation.html > : > > > Query parameters will only be substituted in places where they are > syntactically permissible. See: https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-VAR-SUBST for your choices. > > and > > > Another way to understand this is that variable substitution can only > insert data values into an SQL command; it cannot dynamically change > which database objects are referenced by the command. > > After reading this I am wondering if the current behavior is actually a bug. > > Thanks, > Torsten -- Adrian Klaver adrian.klaver@aklaver.com