Thread: Simple question about running a function.

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!

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!

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
youwish 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()
exampleproduced 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
functionto execute multiple times in the background. 

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

rgs,

SQLP!


> David J.

I thought that the PERFORM "swallowed" the output?

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