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 1pYgaK-0006qK-UF for pgsql-general@arkaria.postgresql.org; Sun, 05 Mar 2023 05:07:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pYgZL-00063R-Qx for pgsql-general@arkaria.postgresql.org; Sun, 05 Mar 2023 05:05:59 +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 1pYgZL-00063C-Fw for pgsql-general@lists.postgresql.org; Sun, 05 Mar 2023 05:05:59 +0000 Received: from mail-qt1-x831.google.com ([2607:f8b0:4864:20::831]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pYgZH-0008CN-Di for pgsql-general@lists.postgresql.org; Sun, 05 Mar 2023 05:05:57 +0000 Received: by mail-qt1-x831.google.com with SMTP id c19so7235918qtn.13 for ; Sat, 04 Mar 2023 21:05:55 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1677992753; h=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=lJaTQG6NdhuSqGFQ0O8EnnFhWYV34IXafKNLbV+vBAM=; b=LrdemqbSpsZQCuXexJreUmJIC8NdNo5A2yctOnCP04V7Q2zbX+7GSedUpa8wKfLBso dHSuzcJi0nDY8w76KC3nhj19wuRVRyU7oEU1yPJpe6GToEHKarYiiIywyiugJfdH6OVw TdtL8M1nSfAZWiSWL4xl4OXSNxyDyigGMDIam7+RaYZmEWvhyW6T/sdJ6inMQuUcjMM+ Ye9qt2Crb0Y3EEM+3sDEz1zP+ToGoKakzRCa9vld89nxFn5b7HnntuC9hl1OiQI7Uxg/ EbmJRdrFMq//JwhywbUauF2bGG9w2CBuyoZzws/7+LvHVVjFJY0HW1f7ZDjPi2/7Fokx T5PQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1677992753; h=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=lJaTQG6NdhuSqGFQ0O8EnnFhWYV34IXafKNLbV+vBAM=; b=vzMoH7c4yD61xYeoPHmTZrVQg14N7leucE9Ly9VP+6NLThteTduxnbtYf591cKdMgn 37svBOJxNJ3vkYrfIk61HiEAd9+CxNPfhPrwuMXCnjFGgdkz/GrINpayx2AZUkchfFPu 8WvLF799U0vTHndYxoCLXaw2jfBUwZEcWpIMSVRE3TjARYZMAOskVZKmt+F1sbRLDCdW UZsh7BplSHuLq+NJQRwMrfdS29iQ1OmoIEpKpVEh9MoHeS82fApssR8a3MzjMxJy1i9g ilMMaMMlqqCsCQQ5mk8Qb0dZAtlTgUWTFKsdOXzCva2BR1iXYB73Z7p44WVMbw/GuWK+ BaIw== X-Gm-Message-State: AO0yUKUFOQYdndESJ8zuE7hpB/zsFFnR9be+j4THW0YWC2lo7kkH+rNH IIMVUnGXHSdrpsr84ufo7Zx64SQ7nIM= X-Google-Smtp-Source: AK7set96ZWSRnihtVXfPAzVNt81ytL4dxpTS/H2NOfK531dlKCXGz1JaJgvNpHbTb3x/O12XJcK/jg== X-Received: by 2002:a05:622a:c2:b0:3b9:e0b2:9a47 with SMTP id p2-20020a05622a00c200b003b9e0b29a47mr11924532qtw.57.1677992753415; Sat, 04 Mar 2023 21:05:53 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id y9-20020ac87089000000b003bfaae103f6sm5027228qto.89.2023.03.04.21.05.53 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 04 Mar 2023 21:05:53 -0800 (PST) Content-Type: multipart/alternative; boundary="------------apzup1Sf3xmuRplw4RlN0Zch" Message-ID: <50b9a782-09a3-e109-b687-90ad96755274@gmail.com> Date: Sat, 4 Mar 2023 23:05:52 -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: "David G. Johnston" Cc: pgsql-general@lists.postgresql.org References: <1337b328-44f7-7f5b-cc1d-b8d11aced2cd@gmail.com> From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------apzup1Sf3xmuRplw4RlN0Zch Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 3/4/23 19:32, David G. Johnston wrote: > On Sat, Mar 4, 2023 at 5:20 PM Ron wrote: > > > But crosstab takes text strings as parameters.  How then do you use \set > variables in crosstab queries? > > > You need to dynamically write the textual query you want to send to the > crosstab function.  In particular that means writing it using "format()" > and then substituting the values into the query via placeholders. > > Roughly like: > > SELECT * FROM crosstab(format('SELECT * FROM %I WHERE %I = %L, > :'tbl_name', :'col_name', :'compare_value')); > > David J. > Ugh.  It's a long and hairy query that would be a nightmare in a format statement. -- Born in Arizona, moved to Babylonia. --------------apzup1Sf3xmuRplw4RlN0Zch Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 3/4/23 19:32, David G. Johnston wrote:
On Sat, Mar 4, 2023 at 5:20 PM Ron <ronljohnsonjr@gmail.com> wrote:

But crosstab takes text strings as parameters.  How then do you use \set
variables in crosstab queries?


You need to dynamically write the textual query you want to send to the crosstab function.  In particular that means writing it using "format()" and then substituting the values into the query via placeholders.

Roughly like:

SELECT * FROM crosstab(format('SELECT * FROM %I WHERE %I = %L, :'tbl_name', :'col_name', :'compare_value'));

David J.


Ugh.  It's a long and hairy query that would be a nightmare in a format statement.

--
Born in Arizona, moved to Babylonia.
--------------apzup1Sf3xmuRplw4RlN0Zch--