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 1pYc7C-0002hN-J2 for pgsql-general@arkaria.postgresql.org; Sun, 05 Mar 2023 00:20:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pYc7B-0006RC-7N for pgsql-general@arkaria.postgresql.org; Sun, 05 Mar 2023 00:20:37 +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 1pYc7A-0006R3-Pt for pgsql-general@lists.postgresql.org; Sun, 05 Mar 2023 00:20:36 +0000 Received: from mail-oi1-x22f.google.com ([2607:f8b0:4864:20::22f]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pYc75-0006Vv-2a for pgsql-general@lists.postgresql.org; Sun, 05 Mar 2023 00:20:35 +0000 Received: by mail-oi1-x22f.google.com with SMTP id bk32so4550173oib.10 for ; Sat, 04 Mar 2023 16:20:30 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1677975630; h=content-transfer-encoding:subject:from:content-language:to :user-agent:mime-version:date:message-id:from:to:cc:subject:date :message-id:reply-to; bh=8xu4qM5UxGn3UUr9c6gKLKKzgkB+0ThzMuWp8s068Dk=; b=ADrc8AefMsShn9SJLWTsfls2TqQMFkeN341zqlRBdP1+SC3e7B3+1k2IcIcTcp5KrG iz97RpCsFWt5942iyVdVC7ffKLyy0xjWaNwM7t2PIsXsYFOtWUcvwRjGvfPS+LujnAUR 5bLGWOgeNeY9hXPD7eW2VonPMOf5dmXdddCrvorj07nSyfsRDltKvWndA7ncAw2lwSa1 XGQMF6leoPUFpdiLXdLskp/1rCI4viWYtAmae7n8FEnUr8SAOCMmv1+TFFrE16+khMH1 9ESVxK7mGxs0VyKysFoXyL4CPRUq+3PNSPRCkjJVHR5Ncg3mZYSQWChN0IbX4XKM9fkL HCZA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1677975630; h=content-transfer-encoding:subject:from:content-language:to :user-agent:mime-version:date:message-id:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=8xu4qM5UxGn3UUr9c6gKLKKzgkB+0ThzMuWp8s068Dk=; b=MDPimJXsmpjrzZFuwPZzRIZ8X7oHY54yza6nL1srGigs/VuxbzsidFh2gtAz5ZdoPv bPf/ouP+82B4YQCPVWIag+lx9bgFRX0L5DeK9Uvx9WwpAf1OlLTTnEgyUS2HqChCpkZ0 NYxnFbFpJ176x27/3cf7f/A6JGyGxRQYyB2YrlcAEIOCQMbt/IaVi/SXQdpzxGIFpJEk TKNy31VaZfcBfgSXr5tnN/A0sbB5ed7mT7V4ePjGJaoEHC4yXu8GnVezCCea/FNbymFL bj4Euii+pig79b5KyLyyeIjxyDlrVuVyodnZORCQDXJf3cdUwWibJP8rSIF/hhP9yNB/ pnhQ== X-Gm-Message-State: AO0yUKW6AnsO9zK5bjBmR10h+ImuvCsQAVFCn0vqlQ7tZ4H4AaujNHkt RCPMRl/q2aWbdkn1paXOSUQoVm/59d4= X-Google-Smtp-Source: AK7set8ofVf6iOItOrTC3k98S4e1RaHc4yBtbVDbHH1VsZ3li8QEtDfGpwmk0SXQ28gIq6EOnG+2Ag== X-Received: by 2002:a05:6808:2343:b0:384:1793:90cf with SMTP id ef3-20020a056808234300b00384179390cfmr3091129oib.0.1677975630197; Sat, 04 Mar 2023 16:20:30 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id y25-20020a4ade19000000b005255e556399sm2338033oot.43.2023.03.04.16.20.29 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 04 Mar 2023 16:20:29 -0800 (PST) Message-ID: <1337b328-44f7-7f5b-cc1d-b8d11aced2cd@gmail.com> Date: Sat, 4 Mar 2023 18:20:29 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 To: pgsql-general@lists.postgresql.org Content-Language: en-US From: Ron Subject: psql \set variables in crosstab queries? 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 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. " $ psql12 -v BOM=2023-02-01 -af foo.sql select :'BOM'::timestamp + interval'6 month';       ?column? ---------------------  2023-08-01 00:00:00 (1 row) select $$ :BOM $$;  ?column? ----------   :BOM (1 row) But crosstab takes text strings as parameters.  How then do you use \set variables in crosstab queries? -- Born in Arizona, moved to Babylonia.