On 11/17/24 02:09, Max Ulidtko wrote:
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
Why not name the client?
provide the "raw" quoted-interpolation substitution (the analogue to
sql.Literal from Adrian example). Instead this query-builder relies on
the parameters mechanism.
Per the docs:
https://www.psycopg.org/psycopg3/docs/api/sql.html
"
class psycopg.sql.Literal(obj: Any)
A Composable representing an SQL value to include in a query.
Usually you will want to include placeholders in the query and pass
values as execute() arguments. If however you really really need to
include a literal value in the query you can use this object.
The string returned by as_string() follows the normal adaptation rules
for Python objects.
Example:
s1 = sql.Literal("fo'o")
s2 = sql.Literal(42)
s3 = sql.Literal(date(2000, 1, 1))
print(sql.SQL(', ').join([s1, s2, s3]).as_string(conn))
'fo''o', 42, '2000-01-01'::date
Changed in version 3.1: Add a type cast to the representation if useful
in ambiguous context (e.g. '2000-01-01'::date)
"
It is meant to pass in a value not something else, say an identifier
which is covered by sql.Identifier. The purpose of the sql module is to
build dynamic SQL safely.
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
<mailto: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
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
