public inbox for [email protected]
help / color / mirror / Atom feedSimple question about running a function.
7+ messages / 2 participants
[nested] [flat]
* Simple question about running a function.
@ 2021-11-26 16:35 SQL Padawan <[email protected]>
2021-11-26 17:36 ` Re: Simple question about running a function. SQL Padawan <[email protected]>
2021-11-26 17:39 ` Re: Simple question about running a function. David G. Johnston <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: SQL Padawan @ 2021-11-26 16:35 UTC (permalink / raw)
To: [email protected] <[email protected]>
Good afternoon all,
I want to test a function. I have \timing on.
There are two ways of doing this - either use lots of data or run it many times.
I'm using psql.
I wish to run my function many times. So, a small dummy function is here:
create or replace function test_fn()
returns VOID as $$
DECLARE
BEGIN
FOR r IN 1..10000 LOOP
SELECT ('a string');
END LOOP;
END;
$$ LANGUAGE plpgsql;
Compiles no problems - CREATE FUNCTION is returned as expected.
So, I try:
SELECT test_fn();
but receive 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() line 6 at SQL statement
So, I searched and found
DO $$ BEGIN
PERFORM test_fn();
END $$;
but when I run this, I receive the same error - i.e. use PERFORM instead.
I tried
PERFORM test_fn();
and receive the error:
ERROR: syntax error at or near "PERFORM"
LINE 1: PERFORM test_fn();
So, could somebody please tell me how to run a function like this?
Any pointers to references/URLs and especially the documentation would be great - I did search but couldn't find anything.
A general overview of the "philosophy" underlying this would be helpful - I'm just not "getting" it?
rgs,
SQLP!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Simple question about running a function.
2021-11-26 16:35 Simple question about running a function. SQL Padawan <[email protected]>
@ 2021-11-26 17:36 ` SQL Padawan <[email protected]>
2021-11-26 17:43 ` Re: Simple question about running a function. David G. Johnston <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: SQL Padawan @ 2021-11-26 17:36 UTC (permalink / raw)
To: SQL Padawan <[email protected]>; +Cc: [email protected] <[email protected]>
What is even more puzzling is that this function works no problem:
create or replace function test_fn_3()
returns VOID as $$
DECLARE
BEGIN
FOR r IN 1..10000 LOOP
INSERT INTO tab VALUES (100);
END LOOP;
END;
$$ LANGUAGE plpgsql;
but this one fails:
create or replace function test_fn_4()
returns VOID as $$
DECLARE
BEGIN
FOR r IN 1..10000 LOOP
SELECT ('A string');
END LOOP;
END;
$$ LANGUAGE plpgsql;
It's something to do with output - SELECT obviously returns something, whereas INSERT doesn't - but that's not strictly true. It returns success or failure?
SELECT test_fn_5() fails:
create or replace function test_fn_5()
returns VOID as $$
DECLARE
BEGIN
FOR r IN 1..10000 LOOP
INSERT INTO tab VALUES (100) RETURNING *;
END LOOP;
END;
$$ LANGUAGE plpgsql;
because of the RETURNING *!
So, it's something to do with output/returned data?
How do I tell psql/PL/pgSQL that I want to suppress the output for
the test?
Trying to run
DO $$ BEGIN SELECT 'blah'; END; $$;
from the command line also fails with the
ERROR: query has no destination for result data
I'd really like to understand what's going on?
SQLP!
> SQLP!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Simple question about running a function.
2021-11-26 16:35 Simple question about running a function. SQL Padawan <[email protected]>
2021-11-26 17:36 ` Re: Simple question about running a function. SQL Padawan <[email protected]>
@ 2021-11-26 17:43 ` David G. Johnston <[email protected]>
2021-11-26 18:33 ` Re: Simple question about running a function. SQL Padawan <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2021-11-26 17:43 UTC (permalink / raw)
To: SQL Padawan <[email protected]>; +Cc: [email protected] <[email protected]>
On Fri, Nov 26, 2021 at 10:37 AM SQL Padawan <[email protected]>
wrote:
>
>
> It's something to do with output - SELECT obviously returns something,
> whereas INSERT doesn't - but that's not strictly true. It returns success
> or failure?
>
SELECT returns a result set (table), Insert doesn't
> INSERT INTO tab VALUES (100) RETURNING *;
>
Unless you use RETURNING...
> 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.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Simple question about running a function.
2021-11-26 16:35 Simple question about running a function. SQL Padawan <[email protected]>
2021-11-26 17:36 ` Re: Simple question about running a function. SQL Padawan <[email protected]>
2021-11-26 17:43 ` Re: Simple question about running a function. David G. Johnston <[email protected]>
@ 2021-11-26 18:33 ` SQL Padawan <[email protected]>
2021-11-26 18:36 ` Re: Simple question about running a function. David G. Johnston <[email protected]>
2021-11-26 18:39 ` Re: Simple question about running a function. David G. Johnston <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: SQL Padawan @ 2021-11-26 18:33 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: [email protected] <[email protected]>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Simple question about running a function.
2021-11-26 16:35 Simple question about running a function. SQL Padawan <[email protected]>
2021-11-26 17:36 ` Re: Simple question about running a function. SQL Padawan <[email protected]>
2021-11-26 17:43 ` Re: Simple question about running a function. David G. Johnston <[email protected]>
2021-11-26 18:33 ` Re: Simple question about running a function. SQL Padawan <[email protected]>
@ 2021-11-26 18:36 ` David G. Johnston <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2021-11-26 18:36 UTC (permalink / raw)
To: SQL Padawan <[email protected]>; +Cc: [email protected] <[email protected]>
On Friday, November 26, 2021, SQL Padawan <[email protected]>
wrote:
>
> I thought that the PERFORM "swallowed" the output?
>
>
“Output” here means a result set (table). Nothing more, nothing less. A
notice is not a table, it’s a text message.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Simple question about running a function.
2021-11-26 16:35 Simple question about running a function. SQL Padawan <[email protected]>
2021-11-26 17:36 ` Re: Simple question about running a function. SQL Padawan <[email protected]>
2021-11-26 17:43 ` Re: Simple question about running a function. David G. Johnston <[email protected]>
2021-11-26 18:33 ` Re: Simple question about running a function. SQL Padawan <[email protected]>
@ 2021-11-26 18:39 ` David G. Johnston <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2021-11-26 18:39 UTC (permalink / raw)
To: SQL Padawan <[email protected]>; +Cc: [email protected] <[email protected]>
On Friday, November 26, 2021, SQL Padawan <[email protected]>
wrote:
>
> 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
>
>
Keep reading this error message until you get it. K is telling you exactly
what, where, and how to fix your problem.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Simple question about running a function.
2021-11-26 16:35 Simple question about running a function. SQL Padawan <[email protected]>
@ 2021-11-26 17:39 ` David G. Johnston <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2021-11-26 17:39 UTC (permalink / raw)
To: SQL Padawan <[email protected]>; +Cc: [email protected] <[email protected]>
On Fri, Nov 26, 2021 at 9:35 AM SQL Padawan <[email protected]>
wrote:
> create or replace function test_fn()
> returns VOID as $$
> DECLARE
> BEGIN
> FOR r IN 1..10000 LOOP
>
> SELECT ('a string');
>
> END LOOP;
> END;
> $$ LANGUAGE plpgsql;
>
> Compiles no problems - CREATE FUNCTION is returned as expected.
>
By default there isn't much compiling going on here. The pl/pgsql code is
just a string that gets executed during query execution.
> So, I try:
>
> SELECT test_fn();
> but receive 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() line 6 at SQL statement
>
It's complaining that "SELECT ('a string')" doesn't have a destination.
The fact that you got it to work when you removed that select and replaced
it with an insert proves that. The CONTEXT line also tells you this in no
uncertain terms.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-11-26 18:39 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-11-26 16:35 Simple question about running a function. SQL Padawan <[email protected]>
2021-11-26 17:36 ` SQL Padawan <[email protected]>
2021-11-26 17:43 ` David G. Johnston <[email protected]>
2021-11-26 18:33 ` SQL Padawan <[email protected]>
2021-11-26 18:36 ` David G. Johnston <[email protected]>
2021-11-26 18:39 ` David G. Johnston <[email protected]>
2021-11-26 17:39 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox