Search Postgresql Archives

Re: Getting error 42P02, despite query parameter being sent

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thanks for replies! I understand now.

Just to clarify user-side motivation: I'm taught that concatenating data into SQL query strings is bad practice and should be avoided. I know how to do it safely in my particular case; but apparently the author of this client library was taught the same, and so their query-builder doesn't provide the "raw" quoted-interpolation substitution (the analogue to sql.Literal from Adrian example). Instead this query-builder relies on the parameters mechanism.

Hence, this limitation forces me to rewrite my query into raw SQL, with hand-quoting of parameter and query string concatenation.

> if CREATE VIEW stores the Param as a Param

This makes zero sense to me... I assumed that $1 would get substituted *at query time*, resulting in effectively VALUES ('md5', 'test-param-value') -- not persisted into the view definition. Which is yes, the former option, Tom; it is sane because that's what $1 does in every other query type.

If I stare into the abyss regardless, and consider the latter option, the one that makes no sense to me... I don't see how could it possibly ever work.

With substitution at some "later time" (expressly not CREATE VIEW query time), how could this ever work?

CREATE VIEW foobar_view (alg, hash) AS VALUES ('md5', $1); -- suppose the Param is persisted into view (?!?)

SELECT * from foobar_view where alg = $1;
— is this a 1- or 2-parameter query?
— what do both $1's refer to exactly?
* there's $1 in select query referring to values in column alg, and
* there's $1 supposedly persisted into VALUES of view definition, referring to a different column with potentially different type.

This makes no sense to me.

So I'm a bit surprised that the (IMO) straightforward semantics of substitution-at-query-time is not supported.

Nevertheless, acknowledging the "patches welcome" status quo sentiment. This is helpful; thanks again.

Max

On сб, лис 16 2024 at 11:51:18 -05:00:00, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Achilleas Mantzios <a.mantzios@xxxxxxxxxxxxxxxxxxxx> 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux