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 ) id 1sb2rv-00DQKF-FM for pgsql-general@arkaria.postgresql.org; Mon, 05 Aug 2024 18:55:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sb2rt-00F8t3-Gj for pgsql-general@arkaria.postgresql.org; Mon, 05 Aug 2024 18:55:41 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sb2rt-00F8ss-2j for pgsql-general@lists.postgresql.org; Mon, 05 Aug 2024 18:55:41 +0000 Received: from mail-lf1-x12a.google.com ([2a00:1450:4864:20::12a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sb2rq-003GAL-CW for pgsql-general@lists.postgresql.org; Mon, 05 Aug 2024 18:55:39 +0000 Received: by mail-lf1-x12a.google.com with SMTP id 2adb3069b0e04-52efa16aad9so15082756e87.0 for ; Mon, 05 Aug 2024 11:55:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1722884136; x=1723488936; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=vU/VzU/U8MLIWumYAMRG7gqsiez8cFyXRBIk2jKCni4=; b=jSyfzXz3Jkpd/PpbHivcffWvTCIkZuL037MkCQ/1dvUBKzN72jTPbyA+PmON564/qT U4JQdV4/mUa9/LNN98D+KXJV8ShTCeEC2pSyXlwdi2YAuz5gOifMAdNT7fy2pFV34gu7 nVvHO41q48H7MeQv0QL0vzYpzP970SqP4pt+kCMSC8B7kyq7FnjFPqJUoWXT+H24oYdX GGtAJyoP06jXrKmQKvqxMRDg38wY7xXv2MR4jkSPsIpKLQRHy/UH0f0KFjuQoJXQh98t MVlCYVPjQPXLioRxYYtJZ0kLRQ8mQgTSTLUR6u8yQ9BiDnystAUMl04hiPfEnJyRPFe9 Whjw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1722884136; x=1723488936; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=vU/VzU/U8MLIWumYAMRG7gqsiez8cFyXRBIk2jKCni4=; b=Lo62QwqbC6sdHzq2KPvmO1ZnIxzo1iyFtu3T33ULrkG9f+2MaBGHBExCN2sHR4pNyq w4toXSYVFZSw5gWANQEtoXWNmi2Hn8fsNU1x/jOTsmeplkXbfHzJLihKCJEtNG1LYzdK uTU2xIpFtd1uNFy1OQEPFtQfGHjQF3v8hgE3rvQ4rlWgb83OmbCx714NJqxZf3j/j9g8 ESC+znkwLUXbhmNLH+3fBNCP0fzto90Oe+XJw6nvBva/EN6r7XQZN7CMiYDCvcha8E10 dEAR7lMj+R+cpOA9u8fOkEjgvJfBo5r46rpbJlQ0XwRv4Vmzk5q0umDt3QLKEomMOb6u vVoA== X-Gm-Message-State: AOJu0Yyjsap/KH3/sm179X2coOxOUKKPH2xn44PPk63lhE8BkqnK9+sg PABN3IIlfAzI4GhTI0K3B8IFiERt/0tfoN5t4T0mCytRD7hb55WPEcg+T+gE8FuyvHSQ0vp0D6w UjUlpT5iXYNSMPtQW5WqrugbmWetAOsw8EA== X-Google-Smtp-Source: AGHT+IEGRmtJzV9FaNWrwaqDNCuOcNjNSP+rh443DDu4aq7Kezqnr+VEEUbLYSGwkqq0kf0nGIR787jxo4pNnldA0zQ= X-Received: by 2002:a05:6512:696:b0:52c:e07d:229c with SMTP id 2adb3069b0e04-530bb36e5e7mr8185204e87.22.1722884135614; Mon, 05 Aug 2024 11:55:35 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: =?UTF-8?Q?Torsten_F=C3=B6rtsch?= Date: Mon, 5 Aug 2024 20:55:24 +0200 Message-ID: Subject: Re: Check psql parameter is passed, if not ask for it To: Wiwwo Staff Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000e4825a061ef438fc" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e4825a061ef438fc Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable to quote the documentation: > If an unquoted colon (:) followed by a psql variable name appears within an argument, it is replaced by the variable's value, as described in SQL Interpolation below. > The forms :'variable_name' and :"variable_name" described there work as well. The :{?variable_name} syntax allows testing whether a variable is defined. It is > substituted by TRUE or FALSE. Escaping the colon with a backslash protects it from substitution. See https://www.postgresql.org/docs/16/app-psql.html On Mon, Aug 5, 2024 at 8:46=E2=80=AFPM Wiwwo Staff wrote: > Hi! > I want to check if I pass the parameter "param1" to a sql script > "delme.sql" like > ``` > \if ':param1' > \echo :param1; > \else > \prompt 'Param1? ' :param1 > \endif > select :'param1'; > ``` > if parameter `param1` is not passed, the .sql should ask for it. > > If I run something like > `psql -f delme.sql -v param1=3D1` > it works, but if i pass > `psql -f delme.sql -v param1=3Dmeh` > i receive > `psql:delme.sql:1: error: unrecognized value ":param1" for "\if > expression": Boolean expected` > > How can I implement that? > TIA, Wiwwo > --000000000000e4825a061ef438fc Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
to quote the documentation:

>=C2=A0I= f an unquoted colon (:) followed by a psql variable name appears within an = argument, it is replaced by the variable's value, as described in SQL I= nterpolation below.
> The forms :'variable_name' and := "variable_name" described there work as well. The :{?variable_nam= e} syntax allows testing whether a variable is defined. It is
>= ; substituted by TRUE or FALSE. Escaping the colon with a backslash protect= s it from substitution.


On Mon, Aug 5, 2024 at 8:46=E2=80=AFPM Wiwwo Staf= f <wiwwo@wiwwo.com> wrote:
=
Hi!
I want to check if I pass the parameter "param1" to = a sql script "delme.sql" like
```
\if ':p= aram1'
=C2=A0 \echo :param1;
\else
=C2=A0 \prompt 'Param1?= ' :param1
\endif
select :'param1';
```
i= f parameter `param1` is not passed, the .sql should ask for it.

If I run something like
`psql -f delme.sql -v param1= =3D1`
it works, but if i pass
`psql -f delme.sql -v param1= =3Dmeh`
i receive
`psql:delme.sql:1: error: unrecogniz= ed value ":param1" for "\if expression": Boolean expect= ed`

How can I implement that?
TIA, Wiwwo
--000000000000e4825a061ef438fc--