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 1pGOSh-0002sK-SY for pgsql-general@arkaria.postgresql.org; Fri, 13 Jan 2023 18:07:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pGOSg-0000In-S3 for pgsql-general@arkaria.postgresql.org; Fri, 13 Jan 2023 18:07:30 +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 1pGOSg-0000Ie-HR for pgsql-general@lists.postgresql.org; Fri, 13 Jan 2023 18:07:30 +0000 Received: from mail-qt1-x832.google.com ([2607:f8b0:4864:20::832]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pGOSe-0003sN-Dx for pgsql-general@lists.postgresql.org; Fri, 13 Jan 2023 18:07:30 +0000 Received: by mail-qt1-x832.google.com with SMTP id k11so2519574qth.3 for ; Fri, 13 Jan 2023 10:07:28 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=NftVyIgYkOqATTZsCGPH8nNTzCTJ7bJNPIy01UjM1nU=; b=Z58q27STzequZ9jixluAfn1E9l7Ud2ECzTW+3dwC2PIGYf84CX73sWAX87gP9an/P0 b2CQxyEz89YVuJd4+QeMP2jJApXroM9g/yfQLKsF54WPHvBzBG1posaguiTnB8LYEWNZ cTSI1PUalTVJ4iZgU+B35+yo6qv55KjvzTL/es44eKoZwkPYk+DEvmaNkl/ow4Zgzf93 4cbofD8g7EuDvOGIsh3/nl+D64N1HzaucwWR30f691ph6OTqe+k27yArcg4HkohpkvXS 9TWkMKbVkSvyRJ1CiRq2huhkdK4U1ei0fb5YmAVJj24TqbiZ4bs+IiM09iq/iopr+OlP 0AbA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references: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=NftVyIgYkOqATTZsCGPH8nNTzCTJ7bJNPIy01UjM1nU=; b=QgUK8PGUy307Qp31PmURja+BhE3NvRj4ikEt+E3IE2rYpJ2/297s66WQfKBtNnqxu8 1GFWZbSlIjifrWDXHBPYcLlaie0XmYvPL/rZmttvNuRJsckTXoBll+faGV+Nmjmcpq+V FVMthY0fu33iB1pz33Imd9ciPW+pa6EVhLcamqcyxOy3cyKRQuVB63OVuHqFO+0iwfdE srRy6JKQnCeFkx2Am+kOr7Vc25uBuhNqX1V8nAguZWFpuqCYnPyuDCyQ/zEjb5U/asAB AYpeNNXicIZgtmz9lX8Ds/r1Kxb2WVq9noxeARpcEE/n1QjpYhWaOf5QKWwODHeBbvkL ROBg== X-Gm-Message-State: AFqh2kr7kd0TD4/cOC1zs8Zb3RiQNWDko0ij99KS1fMGBUk4o583DOKn qe3PWSZf6YjL1mYiBhn/CjAtqbL75q5KEw== X-Google-Smtp-Source: AMrXdXvRX5SUth4gzMbjs8jpKafJKqDS5G0+wtqX5CJXEVpEqWrdclv6JIECAcnH6YOftvQ18uMLOg== X-Received: by 2002:a05:622a:5a0e:b0:3ad:17ca:c3f with SMTP id fy14-20020a05622a5a0e00b003ad17ca0c3fmr25113684qtb.35.1673633246442; Fri, 13 Jan 2023 10:07:26 -0800 (PST) Received: from [192.168.1.66] (108-250-206-84.lightspeed.nworla.sbcglobal.net. [108.250.206.84]) by smtp.googlemail.com with ESMTPSA id c14-20020ac84e0e000000b003ae450e43acsm2534826qtw.12.2023.01.13.10.07.26 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 13 Jan 2023 10:07:26 -0800 (PST) Content-Type: multipart/alternative; boundary="------------dre3ecYEFu376jfIbmsYOp0U" Message-ID: <76773204-7c6c-32ba-3dd9-4014267bce26@gmail.com> Date: Fri, 13 Jan 2023 12:07:25 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: Directly embedding a psql SET variable inside another string? Content-Language: en-US To: "pgsql-general@lists.postgresql.org" References: 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. --------------dre3ecYEFu376jfIbmsYOp0U Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 1/13/23 11:13, David G. Johnston wrote: > On Fri, Jan 13, 2023 at 9:12 AM Ron wrote: > > > is there any way to *directly* embed v_ssn in another string? > > > No > As expected, this fails: > > postgres=# SELECT * FROM employee WHERE ssn LIKE :'%v_ssn%'; > ERROR:  syntax error at or near ":" > LINE 1: SELECT * FROM employee WHERE ssn LIKE :'%v_ssn%'; > > > Two options: > > format('%%%s%%', :'v_ssn') This one looks clean, and works perfectly: > '%' || :'v_ssn' || '%' -- Born in Arizona, moved to Babylonia. --------------dre3ecYEFu376jfIbmsYOp0U Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 1/13/23 11:13, David G. Johnston wrote:
On Fri, Jan 13, 2023 at 9:12 AM Ron <ronljohnsonjr@gmail.com> wrote:

is there any way to directly embed v_ssn in another string?

No
As expected, this fails:

postgres=# SELECT * FROM employee WHERE ssn LIKE :'%v_ssn%';
ERROR:  syntax error at or near ":"
LINE 1: SELECT * FROM employee WHERE ssn LIKE :'%v_ssn%';


Two options:

format('%%%s%%', :'v_ssn')

This one looks clean, and works perfectly:

'%' || :'v_ssn' || '%'

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