MIME-Version: 1.0
References: <837985448.725696.1732374064323@sg2000-ap-3>
In-Reply-To: <837985448.725696.1732374064323@sg2000-ap-3>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Sat, 23 Nov 2024 16:57:49 +0100
Message-ID: <CAFj8pRD2cBkTKafBYn1vUDSYp8CN_3XVk4+NA55g4J5jCHrC_Q@mail.gmail.com>
Subject: Re: PQexecParams and "SET TIME ZONE $1" gets 'syntax error at or near
 "$1" at character 15'
To: ma.sao@msa.hinet.net
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000d2555906279691ec"
Archived-At: <https://www.postgresql.org/message-id/CAFj8pRD2cBkTKafBYn1vUDSYp8CN_3XVk4%2BNA55g4J5jCHrC_Q%40mail.gmail.com>
Precedence: bulk

--000000000000d2555906279691ec
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi

so 23. 11. 2024 v 16:01 odes=C3=ADlatel <ma.sao@msa.hinet.net> napsal:

> I get get this same error
>
> syntax error at or near "$1" at character 15
>
> if I feed "const char *command" with the following texts.
>
> SET TIME ZONE $1
> SET TIME ZONE $1::TEXT
>
> For some reasons, I can not add quotes around $1 as follows.
>
> SET TIME ZONE '$1'
> SET TIME ZONE '$1'::TEXT
>
> Statements like "SELECT ... WHERE $1=3D..." have been working flawlessly.
>
> It looks like PQexecParams doesn't handle statement with "SET ... $1".
>
> Are there workarounds for me or PQexecParams needs improvement?
>
This statement has no plan, and then doesn't support parametrization.

you can use a function set_config instead
https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMI=
N-SET

SELECT set_config('timezone', $1, false)

Regards

Pavel


> Thank you!
>
> --- =E6=9C=AC=E9=83=B5=E4=BB=B6=E4=BE=86=E8=87=AAHiNet WebMail ---
>

--000000000000d2555906279691ec
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi<br></div><br><div class=3D"gmail_quote"><div dir=
=3D"ltr" class=3D"gmail_attr">so 23. 11. 2024 v=C2=A016:01 odes=C3=ADlatel =
&lt;<a href=3D"mailto:ma.sao@msa.hinet.net">ma.sao@msa.hinet.net</a>&gt; na=
psal:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0p=
x 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><p>I get g=
et this same error<br><br>syntax error at or near &quot;$1&quot; at charact=
er 15<br><br>if I feed &quot;const char *command&quot; with the following t=
exts.</p>
<p></p>
<p>SET TIME ZONE $1<br>SET TIME ZONE $1::TEXT</p>
<p></p>
<p>For some reasons, I can not add quotes around $1 as follows.</p>
<p></p>
<p>SET TIME ZONE &#39;$1&#39;<br>SET TIME ZONE &#39;$1&#39;::TEXT</p>
<p></p>
<p>Statements like &quot;SELECT ... WHERE $1=3D...&quot; have been working =
flawlessly.<br><br>It looks like PQexecParams doesn&#39;t handle statement =
with &quot;SET ... $1&quot;.=C2=A0</p>
<p></p>
<p>Are there workarounds for me or PQexecParams needs improvement?</p></blo=
ckquote><div>This statement has no plan, and then doesn&#39;t support param=
etrization.</div><div><br></div><div>you can use a function set_config inst=
ead <a href=3D"https://www.postgresql.org/docs/current/functions-admin.html=
#FUNCTIONS-ADMIN-SET">https://www.postgresql.org/docs/current/functions-adm=
in.html#FUNCTIONS-ADMIN-SET</a></div><div><br></div><div>SELECT set_config(=
&#39;timezone&#39;, $1, false)<br></div><div><br></div><div>Regards</div><d=
iv><br></div><div>Pavel</div><div><br></div><div><br></div><div>=C2=A0</div=
><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border=
-left:1px solid rgb(204,204,204);padding-left:1ex">
<p></p>
<p>Thank you!</p><br><div><span style=3D"font-size:13px">--- =E6=9C=AC=E9=
=83=B5=E4=BB=B6=E4=BE=86=E8=87=AAHiNet WebMail ---</span></div></blockquote=
></div></div>

--000000000000d2555906279691ec--