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 1lK0aI-000350-Sw for pgsql-bugs@arkaria.postgresql.org; Wed, 10 Mar 2021 15:17:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lK0aH-0001rC-Pt for pgsql-bugs@arkaria.postgresql.org; Wed, 10 Mar 2021 15:17:13 +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 1lK0aH-0001qN-8n for pgsql-bugs@lists.postgresql.org; Wed, 10 Mar 2021 15:17:13 +0000 Received: from mail-qt1-x82d.google.com ([2607:f8b0:4864:20::82d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lK0aE-000443-Qf for pgsql-bugs@lists.postgresql.org; Wed, 10 Mar 2021 15:17:12 +0000 Received: by mail-qt1-x82d.google.com with SMTP id z6so8115062qts.2 for ; Wed, 10 Mar 2021 07:17:10 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=joeconway.com; s=google; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language:content-transfer-encoding; bh=QMpWTAKVGxBXE1uKzOheu9yJ2C6J9ZSfSILkm0aBbRE=; b=cXqOX5o7KCL6XectV5SxqZ90+itCYUhG8ON9+wpO+3rKb0BCo6l3m847Io/SgVocRF cHLvIu+WH1D/W3gIJ3nlaUJ+UirwgFGH2dfP/hLwTek6MiY7voPEIJiEGi3ohnwe1laj jkKWbPyp5RpRQ06nrgx+2uDF6WhbI30UbBgI7yrQ0UzBPlyVZb/VZIYJiBs4OxFYb39T KHS3wwDIa5bHlIoscR2oYmb3wryEb5qmApReX2OwLb4sS7uDLMiTBlzyY72gHlKoPJeS v7IZgDt/Tnc6RnsOIhAuRumsf4D7GlkTi9dwSoaCDCcl6f/i2uiE/UNsaddQLgHsKwB1 3YBQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language :content-transfer-encoding; bh=QMpWTAKVGxBXE1uKzOheu9yJ2C6J9ZSfSILkm0aBbRE=; b=Q61RApnHdo4Omy0tNZpO0nULkU8QxvLkkYKKrZzmm+LUYP7F7SpVY4dgIE+Lx6lW2h nh6gLJzDOf7dDqXZid34vq1pqKDTL6IFPGnqiKSVs9owbP2wz78pUAkVC4ZFb7U2JI6E 0ZcoBAhNjYaRIw5yv66GDdmMZAxObuarwnwZwGmwiSoRkyUclPx+BfYzJ7k1jH754yPK iWlsI6+nmi6eYFaFW3N+Va/Run6njhv3YHWc4zTlHEP0EFeWE9WIMXqsJ4F+6SBuXI7w yzNK2mN1qKnOX+4NM37F36xvZQTf6qoVuJmIEda/uxLDnwRH3rCHuMdglyl8JLiytCLF P5mA== X-Gm-Message-State: AOAM5301NXVv8orFU65TvaVKJkTTAe4FA5IuTx1KBhSsHuHwuQ8Gwfim Zf22ufCyRGfIzHfNliBQb/nPgVAjhcXU88xn47TDBrS/mrw7LMP0ZLiZ8Ll7PR+YxPyHkcTIpz0 J/MejArxc+0OA3LLeQFjBKvkeB5UbkZxxKTWIfrSzUsVfmkv8M1QST18vZW84zw0FWD+kV2Yly+ XNIFcR X-Google-Smtp-Source: ABdhPJyBo+qEe1OFzvpL3hANLNz4YUzUDOC9BujBSHHgCbs7VBSo7svAaGkliGyIO5JRc6DnzGJElA== X-Received: by 2002:aed:3741:: with SMTP id i59mr1183662qtb.303.1615389428668; Wed, 10 Mar 2021 07:17:08 -0800 (PST) Received: from [192.168.4.41] (072-017-018-098.res.spectrum.com. [72.17.18.98]) by smtp.gmail.com with ESMTPSA id z78sm13207350qka.38.2021.03.10.07.17.07 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 10 Mar 2021 07:17:08 -0800 (PST) Subject: Re: COPY TO (query) allows no function arguments To: =?UTF-8?Q?F=c3=a4rber=2c_Franz-Josef_=28StMUK=29?= , "pgsql-bugs@lists.postgresql.org" References: <609326d4d6854085aeb909ae564d4ccb@stmuk.bayern.de> From: Joe Conway Message-ID: Date: Wed, 10 Mar 2021 10:17:07 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.7.1 MIME-Version: 1.0 In-Reply-To: <609326d4d6854085aeb909ae564d4ccb@stmuk.bayern.de> Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 3/10/21 9:48 AM, Färber, Franz-Josef (StMUK) wrote: > it is as simple as the subject says: > > The COPY TO (query) statement does not allow the passing of function arguments – > is this by intent or a bug? This is not a bug -- next time if you are uncertain please try the general list first. In any case, you cannot use the passed in argument text as part of a simple SQL command -- you have to build the command from text and then execute it. For example: 8<------------------------ CREATE TABLE b(id int); INSERT INTO b VALUES(1),(2),(42); CREATE OR REPLACE FUNCTION a(myparam text) RETURNS void LANGUAGE plpgsql AS $func$ BEGIN EXECUTE $$COPY (SELECT * FROM $$ || myparam || $$) TO '/tmp/a.txt'$$; END; $func$; SELECT a('b'); SELECT pg_read_file('/tmp/a.txt'); pg_read_file -------------- 1 + 2 + 42 + (1 row) 8<------------------------ HTH, Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development