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 1pYgXF-0006hN-Tp for pgsql-general@arkaria.postgresql.org; Sun, 05 Mar 2023 05:03:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pYgXC-0002FR-PW for pgsql-general@arkaria.postgresql.org; Sun, 05 Mar 2023 05:03:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pYgXC-0002FI-D0 for pgsql-general@lists.postgresql.org; Sun, 05 Mar 2023 05:03:46 +0000 Received: from mail-qt1-x829.google.com ([2607:f8b0:4864:20::829]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pYgX5-0000SG-LP for pgsql-general@lists.postgresql.org; Sun, 05 Mar 2023 05:03:45 +0000 Received: by mail-qt1-x829.google.com with SMTP id h19so7273559qtk.7 for ; Sat, 04 Mar 2023 21:03:39 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1677992618; h=content-transfer-encoding:in-reply-to:from:references:cc:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=vseKt6FAeiJcI5loPQrzvTVQZ3g9OSVcEa6wQIvFHOw=; b=JS0QzJHQ6XtriHzABzBJE/gQHPYDWQ1stShyjchhvmzovLtQhuL/pJeRQ8gqWvyUFB /piGCE1iHzOib8kevfIxkkb2YmQPtZxQ3XwLClh77pJZUa2ZcDCosdeOTwLFRtREA7t6 j1RccdJGwjupddmmJAkQ7mycbLfY7NeYkfVEf6cKZp89CGt4eFjRGsbG7vwgfetONw1h hUYZBPVKHFRyJf0mUv57JHc4/XtTt2ulepmZKGxh/fV5qpD6nNrHWC1LfS+TfP9krn7c XUfWWel+O1xJr9pX/R+RGD6gDFz7DQYXa6NCaSlOMKoyIjWxSQTB3l4zWji4F6wrRN7S 1/Qg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1677992618; h=content-transfer-encoding:in-reply-to:from:references:cc:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=vseKt6FAeiJcI5loPQrzvTVQZ3g9OSVcEa6wQIvFHOw=; b=S8Kuy870qpZSUk7+IGnJXhoDv059Yy7V85UIghBrbaBJqvcp5m6aXypxbPsjNEK7QA BkBiJmuiTZJeq/roHa6aPLs8Z5OyBVkuqrT1p4WSJGGPMYKWS38OVGxKkUn5riz9IyhP arfP5vhul+67VE2ifXkmFJyb8wifY/+zBMArrzIgO1TpzxywcDVc0hbeVBSzfQnErlOC nsub2Wq5EDTnqgutk0XjDIGEDKhDschxKRWF1yi/1MQN1HB0WzLgQfjwCbRS04EVjXqu OXe9UuFwWCnVOGmB7vfwRsZUZQnw0MulXHWmRIh76an7s2BEtX0uJ15QCOS4wpJyfe4N XXjg== X-Gm-Message-State: AO0yUKXrSDh4GiFg80RcKdgIIYfwKutt0yKrZOaDDIhstaBW1aXfQpp4 eU0Y3TXEoOpW1ZtddcyukcI= X-Google-Smtp-Source: AK7set8bep6Y98O/B8DwCWloq8U6taiOZGCVHz2lLu7L07le+ecaiIqwfxG5bOhU+zyZgang2hM+mQ== X-Received: by 2002:ac8:7f4a:0:b0:3bf:dae3:bde2 with SMTP id g10-20020ac87f4a000000b003bfdae3bde2mr12305729qtk.36.1677992618678; Sat, 04 Mar 2023 21:03:38 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id f8-20020a05620a280800b0074269db4699sm5067418qkp.46.2023.03.04.21.03.37 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 04 Mar 2023 21:03:38 -0800 (PST) Message-ID: <7fa041bf-bca9-4a2b-f1cc-ebdcb7b76dd2@gmail.com> Date: Sat, 4 Mar 2023 23:03:37 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: psql \set variables in crosstab queries? Content-Language: en-US To: Tom Lane Cc: pgsql-general@lists.postgresql.org References: <1337b328-44f7-7f5b-cc1d-b8d11aced2cd@gmail.com> <3054299.1677979338@sss.pgh.pa.us> From: Ron In-Reply-To: <3054299.1677979338@sss.pgh.pa.us> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 3/4/23 19:22, Tom Lane wrote: > Ron writes: >> According to >> https://www.postgresql.org/docs/12/app-psql.html#APP-PSQL-VARIABLES and >> experience, variables don't interpolate inside of string literals: >> " >> Variable interpolation will not be performed within quoted SQL literals and >> identifiers. Therefore, a construction such as ':foo' doesn't work to >> produce a quoted literal from a variable's value. >> " >> But crosstab takes text strings as parameters.  How then do you use \set >> variables in crosstab queries? > If you read a little further, you'll find out the syntax for converting > the value of a psql variable to a SQL string literal: > > regression=# \set foo BAR > regression=# select ':foo'; > ?column? > ---------- > :foo > (1 row) > > regression=# select :'foo'; > ?column? > ---------- > BAR > (1 row) > > What the server got in the last case was "select 'BAR';". postgres=# \set foo BAR postgres=# select :'foo';  ?column? ----------  BAR (1 row) postgres=# select $$ :foo $$;  ?column? ----------   :foo (1 row) postgres=# select $$ :'foo' $$;  ?column? ----------   :'foo' (1 row) -- Born in Arizona, moved to Babylonia.