Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1tCM2B-00H8af-C3
	for pgsql-general@arkaria.postgresql.org; Sat, 16 Nov 2024 16:52:30 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1tCM19-00F4qC-4r
	for pgsql-general@arkaria.postgresql.org; Sat, 16 Nov 2024 16:51:27 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1tCM18-00F4q4-QC
	for pgsql-general@lists.postgresql.org; Sat, 16 Nov 2024 16:51:27 +0000
Received: from sss.pgh.pa.us ([68.162.161.243])
	by magus.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1tCM16-002HgN-4K
	for pgsql-general@lists.postgresql.org; Sat, 16 Nov 2024 16:51:26 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 4AGGpIaX2757247;
	Sat, 16 Nov 2024 11:51:18 -0500
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
cc: Max Ulidtko <ulidtko@gmail.com>, pgsql-general@lists.postgresql.org
Subject: Re: Getting error 42P02, despite query parameter being sent
In-reply-to: <40d8beef-ff67-4c6c-828c-2941ca30fdef@cloud.gatewaynet.com>
References: <NCI1NS.TUGY51AF5VWQ3@gmail.com> <40d8beef-ff67-4c6c-828c-2941ca30fdef@cloud.gatewaynet.com>
Comments: In-reply-to Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
	message dated "Sat, 16 Nov 2024 13:15:45 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <2757245.1731775878.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Sat, 16 Nov 2024 11:51:18 -0500
Message-ID: <2757246.1731775878@sss.pgh.pa.us>
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/2757246.1731775878%40sss.pgh.pa.us>
Precedence: bulk

Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> writes:
> Στις 16/11/24 12:55, ο/η Max Ulidtko έγραψε:
>> The issue I'm hitting with it is exemplified by server logs like this:
>> 
>> 2024-11-16 10:28:19.928 UTC [46] LOG: execute <unnamed>: CREATE VIEW 
>> public.foobar (alg, hash) AS VALUES ('md5', $1);
>> 2024-11-16 10:28:19.928 UTC [46] DETAIL: parameters: $1 = 
>> 'test-param-value'
>> 2024-11-16 10:28:19.928 UTC [46] ERROR: there is no parameter $1 at 
>> character 57

> At least for SQL level prepared statements the statement has to be one of :
> |SELECT|, |INSERT|, |UPDATE|, |DELETE|, |MERGE|, or |VALUES|
> |so CREATE is not valid, and I guess the extended protocol prepared 
> statements aint no different in this regard.

Indeed.  To some extent this is an implementation limitation: the
parameter is received (and printed if you have logging enabled),
but it's not passed down to utility statements such as CREATE VIEW.
But the reason nobody's been in a hurry to lift that restriction
is that doing so would open a large can of semantic worms.  In a
case like CREATE VIEW, exactly what is this statement supposed to
mean?  I assume you were hoping that it would result in replacement
of the Param by a Const representing the CREATE-time value of the
parameter, but why is that a sane definition?  It's certainly not
what a Param normally does.  On the other hand, if CREATE VIEW
stores the Param as a Param (which is what I think would happen
if we just extended the parameter-passing plumbing), that's unlikely
to lead to a good outcome either.  There might not be any $1 available
when the view is used, and if there is one it's not necessarily of
the right data type.

So, pending some defensible design for what should happen and a patch
implementing that, we've just left it at the status quo, which is that
Params are only available to the DML statements Achilleas mentioned.

			regards, tom lane