Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1puBly-0006pp-De for pgsql-general@arkaria.postgresql.org; Wed, 03 May 2023 12:39:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1puBlw-00060v-Qm for pgsql-general@arkaria.postgresql.org; Wed, 03 May 2023 12:39:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1puBlw-00060m-GR for pgsql-general@lists.postgresql.org; Wed, 03 May 2023 12:39:52 +0000 Received: from mout-u-107.mailbox.org ([80.241.59.207]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1puBlt-000LN6-Eb for pgsql-general@lists.postgresql.org; Wed, 03 May 2023 12:39:51 +0000 Received: from smtp202.mailbox.org (smtp202.mailbox.org [10.196.197.202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-107.mailbox.org (Postfix) with ESMTPS id 4QBGjt5Cpvz9sSB; Wed, 3 May 2023 14:39:46 +0200 (CEST) Date: Wed, 3 May 2023 14:39:45 +0200 (CEST) From: Erik Wienhold To: "J.A." , pgsql-general@lists.postgresql.org Message-ID: <658455455.1152531.1683117585087@office.mailbox.org> In-Reply-To: References: Subject: Re: PL/pgSQL doesn't support variables in queries? MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Priority: 3 Importance: Normal List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 03/05/2023 14:25 CEST J.A. wrote: > > ms-sql person here migrating over to pgsql. One of the first thing's I no= ticed > with pgsql (or more specifically, PL/pgSQL) is that it doesn't support > "variables" in a query? > > for example, here's some T-SQL: > > DECLARE=C2=A0@fkId INTEGER > > SELECT=C2=A0@fkId =3D fkId FROM SomeTable WHERE id =3D 1 > > -- and then do something with that value.. > > SELECT * FROM AnotherTable WHERE Id =3D=C2=A0@fkId > SELECT * FROM YetAnotherTable WHERE FKId =3D=C2=A0@fkId > -- etc.. plpgsql does support variable declarations [0] but does not use any special notation like T-SQL. An equivalent to your example would be: =09DO $$ =09DECLARE =09 v_fkid int; =09 v_rec record; =09BEGIN =09 SELECT fkid INTO v_fkid FROM SomeTable WHERE id =3D 1; =09 SELECT * INTO v_rec FROM AnotherTable WHERE Id =3D v_fkid; =09 -- Do something with v_rec ... =09END $$; Prefixing variable names with v_ is just a convention to avoid ambiguous co= lumn references (assuming that column names are not prefixed with v_) [1]. [0] https://www.postgresql.org/docs/current/plpgsql-declarations.html [1] https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLP= GSQL-VAR-SUBST -- Erik