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 1jsyDj-0002Xc-Ao for pgsql-admin@arkaria.postgresql.org; Wed, 08 Jul 2020 00:45:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jsyDi-0006Cp-90 for pgsql-admin@arkaria.postgresql.org; Wed, 08 Jul 2020 00:45:54 +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 1jsyDh-0006Ci-TX for pgsql-admin@lists.postgresql.org; Wed, 08 Jul 2020 00:45:54 +0000 Received: from mail-oo1-xc2f.google.com ([2607:f8b0:4864:20::c2f]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jsyDb-0000vs-5v for pgsql-admin@lists.postgresql.org; Wed, 08 Jul 2020 00:45:53 +0000 Received: by mail-oo1-xc2f.google.com with SMTP id 127so4982835ooc.9 for ; Tue, 07 Jul 2020 17:45:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=nKhKPyW+h53i731aTEb7/eXeW0a6xw6X0Ykw45YZtN4=; b=h2fjYFpkZftDBMhopfg3pbHYu3YiG1zazEHH0aQKMrt37fEF1xbP9yDTAU/HIc7E+n nvmbkseZeu/U2ZGVLWqcWGrSLmXkZvL/MqvA5Ul89AxGlMhj1A1WvfEBnvQ2DdZNscoX OdjwqnCYmSNL+l5zHARmEQr3GmMonbZr47nfFFw7AQAmP0DZAO421YhOJKVn7rJEdxXu OW0fHYW2sh9sbkKla5c4zKMKajoOa2S2omOKjJqPqlx3xQ3Q2Oo1OcYU1ZsVCA6BpnZM 4HE6gTnN8txFKyCJNDYHnTDX+JPJE18lSu2czpv6ty1ReGhL0fHSVPMHqmbVjam+fUse OGAQ== 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; bh=nKhKPyW+h53i731aTEb7/eXeW0a6xw6X0Ykw45YZtN4=; b=ST6hAQPKDENxqiS10bfYYyKoE+N3lfNx+gsdrpwQES5uZCz3f//JnEGsu8I0WEOa+/ qHvUhdl2h73rYB36ogvnEfhpSuVkVc0+gaW/FP19Xkzpg1Hu2iLVgvclUwUfVAUnHZJR +OHmacyjrq1TIYS6at53lPI/34/UK3BJuMA9d1CKZxqRV55fuBfpN21qMEUYJMHUCCZR +90ycffnFeYDr8Nay1kWrFL3z/vE6+Pam3Nnhv2vsTXOQlMLyPazEFZdeAe/cCSws8se 4qiKYeGslEy4VSYyPZtbzraRsiNsrP0AdHPShwxJ1cFT5qzkgCQrtm2ri10LmcIa14C6 IpUQ== X-Gm-Message-State: AOAM531ldSkKMU8mpbF4psBFyjNcCpRCO6M5GLQSMNW5tYrnFvp6Q+97 Nn1Vz3Afq9ZxtdNHvAcnWAVVDP/nXyxOuQ== X-Google-Smtp-Source: ABdhPJwewv5FoBQCxrdTDs+YtnL7vrW4zkinyZmMMlyMEY9nbu5jmbOT4tTmbnGWYmP6rDAse/mTwA== X-Received: by 2002:a4a:83d5:: with SMTP id r21mr38335026oog.19.1594169144548; Tue, 07 Jul 2020 17:45:44 -0700 (PDT) Received: from [192.168.1.10] (ip70-186-126-197.no.no.cox.net. [70.186.126.197]) by smtp.googlemail.com with ESMTPSA id u19sm425678oic.10.2020.07.07.17.45.43 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 07 Jul 2020 17:45:43 -0700 (PDT) Subject: Re: SELECTing from a function where i don't want the results To: pgsql-admin@lists.postgresql.org References: From: Ron Message-ID: <911059a0-d138-7ea3-044e-0d7ca71755a2@gmail.com> Date: Tue, 7 Jul 2020 19:45:42 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.8.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------6BF5169CC18436CE54433AA2" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------6BF5169CC18436CE54433AA2 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Your original question says that the function returns the number of rows deleted.  That's one line.  Now you're saying that it returns one row per table-row deleted.  Which is it? On 7/7/20 7:21 PM, Wells Oliver wrote: > ha, the CTE approach to only get one line of output versus however many > hundreds of rows were used for the delete is perfect. Thanks. > > On Tue, Jul 7, 2020 at 5:18 PM David G. Johnston > > wrote: > > On Tuesday, July 7, 2020, Wells Oliver > wrote: > > Vanilla SQL script calls a plpgsql function to delete some number > of rows from three tables: > > SELECT mydelete(r) FROM sometable; > > Where sometable contains maybe 100+ records. This causes the > results from the function (integer of number of rows removed) to > be displayed in the output, like you'd kinda expect with a SELECT > call, except I don't want to see it all, I just want the function > quietly executed and rows removed. > > Can I accomplish this? > > > Pure SQL, no, you cannot just ignore the output.  You can perform > post-processing (via CTE/WITH) to reduce how much is printed > (aggregates). If you are using psql you can send it to /dev/null.  You > could use a DO block and (kinda) ignore the result (SQL) and/or stick > it into a throw-away variable (plpgsql). > > David J. > > > > -- > Wells Oliver > wells.oliver@gmail.com -- Angular momentum makes the world go 'round. --------------6BF5169CC18436CE54433AA2 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Your original question says that the function returns the number of rows deleted.  That's one line.  Now you're saying that it returns one row per table-row deleted.  Which is it?

On 7/7/20 7:21 PM, Wells Oliver wrote:
ha, the CTE approach to only get one line of output versus however many hundreds of rows were used for the delete is perfect. Thanks.

On Tue, Jul 7, 2020 at 5:18 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tuesday, July 7, 2020, Wells Oliver <wells.oliver@gmail.com> wrote:
Vanilla SQL script calls a plpgsql function to delete some number of rows from three tables:

SELECT mydelete(r) FROM sometable;

Where sometable contains maybe 100+ records. This causes the results from the function (integer of number of rows removed) to be displayed in the output, like you'd kinda expect with a SELECT call, except I don't want to see it all, I just want the function quietly executed and rows removed.

Can I accomplish this?


Pure SQL, no, you cannot just ignore the output.  You can perform post-processing (via CTE/WITH) to reduce how much is printed (aggregates). If you are using psql you can send it to /dev/null.  You could use a DO block and (kinda) ignore the result (SQL) and/or stick it into a throw-away variable (plpgsql).

David J.



--

--
Angular momentum makes the world go 'round.
--------------6BF5169CC18436CE54433AA2--