public inbox for [email protected]  

help / color / mirror / Atom feed

From: SQL Padawan <[email protected]>
To: David G. Johnston <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Simple question about running a function.
Date: Fri, 26 Nov 2021 18:33:01 +0000
Message-ID: <CBHeX2fE8wfCaa2pRekG8NAVM0-ZOphl6XGAnFt-ZDWMwKmnVAwunxntpLigPLZWhh64aAa3P7NpUk5Ax_1ujqUNUm4iG2HWmjhOa4s-vbk=@protonmail.com> (raw)
In-Reply-To: <CAKFQuwYvFhfuLiAfjef-nB-abrY12bgr4oD0we=vMkjNmoT81g@mail.gmail.com>
References: <auapJ5ZJCo1pvdzR6GK_1Fdqi7utxKRWBiCA-C0DrSZDyW5aBGNhlcvrxe4gGJOEKr6UpB7or2KwaOocBqudGlkX-OqEdriszoj5awt3ptU=@protonmail.com>
	<I_azqsn6cR13UFT5V7_Q8SYvAMXs71wWZ_GvVQnTEEuNk2ilyPNKUbJsbKAaLXLindkisjBr2-7Ed1kfKsIubulp-HQCNXnRiltQiTS9rXk=@protonmail.com>
	<CAKFQuwYvFhfuLiAfjef-nB-abrY12bgr4oD0we=vMkjNmoT81g@mail.gmail.com>


Hi David and thank you for trying to help the terminally stupid!


> > How do I tell psql/PL/pgSQL that I want to suppress the output for
> > the test?

> By doing exactly what the error message told you.  Use PERFORM instead of SELECT...on the exact query whose output you wish to ignore, not an indirect caller.

OK - so, following your input, I searched further and I found a post by Pavel Stehule - great I thought, a major contributor - see here:
https://stackoverflow.com/a/42922661/470530

his example is this function which I duly created (and not, as you pointed out, compiled!)

CREATE OR REPLACE FUNCTION foo()
RETURNS void AS $$
BEGIN
  RAISE NOTICE 'Hello from void function';
END;
$$ LANGUAGE plpgsql;

which I run as a direct call as in the example.

test=# SELECT foo();
NOTICE:  Hello from void function
 foo
-----
(1 row)

So, then I ran this.

-- in PLpgSQL
DO $$
BEGIN
  PERFORM foo(); -- is ok
END;
$$;

with a result I found surprising - as follows.

NOTICE:  Hello from void function
DO
Time: 8.704 ms

I thought that the PERFORM "swallowed" the output?


So, then I went back to my own function:


create or replace function test_fn_6()
returns VOID as $$
DECLARE
BEGIN
  FOR r IN 1..10000 LOOP

    SELECT ('A string');

  END LOOP;
END;
$$ LANGUAGE plpgsql;

and then I tried.

DO $$
BEGIN
  PERFORM test_fn_6();
END;
$$;

but get the error.

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function test_fn_6() line 6 at SQL statement
SQL statement "SELECT test_fn_6()"
PL/pgSQL function inline_code_block line 3 at PERFORM


Now, I thought from Pavel Stehule's post that my function's output would/should be swallowed - but then the foo() example produced a text output?

I'm really puzzled here... I mean what I'm asking for is really simple - pl/pgsql 101 and I can't even get a simple function to execute multiple times in the background.

I'm not asking for the sun, moon and stars here...

rgs,

SQLP!


> David J.

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Simple question about running a function.
  In-Reply-To: <CBHeX2fE8wfCaa2pRekG8NAVM0-ZOphl6XGAnFt-ZDWMwKmnVAwunxntpLigPLZWhh64aAa3P7NpUk5Ax_1ujqUNUm4iG2HWmjhOa4s-vbk=@protonmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox