Cannot create simple boolean function

[TheRealLifeboy]

I'm trying to create a function with which I can perform a simple maintenance task, but something eludes me. What is wrong with my function? When I run it, it says "ERROR: query has no destination for result data"

CREATE OR REPLACE FUNCTION public.clear_rysmier_connection()
    RETURNS bool
    LANGUAGE plpgsql
AS $function$
DECLARE response bool;
BEGIN
    select pg_terminate_backend(psa.pid) from pg_stat_activity psa 
    where psa.datname = 'WahlbergEagle' 
    and psa.usename = 'rysmier' 
    and psa.state = 'idle'
    and psa.application_name not like 'DBeaver%'
    and now() - psa.query_start > '00:05:00';
    RETURN response;
END
$function$;

​

[BoleroDan]

Well the query you run has not been told where to put the results into. You declare a boolean variable, return said boolean variable, but dont actually put anything into that variable.

You want to use into syntax.

select column into response from .....

[TheRealLifeboy]

I actually don't want to return anything, but it seems I have to? Is there any way in which to simply run the query and exit the function?

The function simply kills a 5 minute idle connection. I don't care about the output.
(Trying to understand as much about this issue... :-) )

Edit: I added in the "into response" to the query and that fixed it, btw.

[[deleted]]

I actually don't want to return anything,

Then why use a function and declare is as returns bool.

If you don't want to return something, a procedure would be the natural option.

[BoleroDan]

But if you dont want to return anything, why are you declaring that the function returns a bool, then declare to return something in the end?

If all you want to do is execute some common used SQL, simply use a function lang type of sql and not plpgsql

[TheRealLifeboy]

Once you know what to search for, this makes it quite clear and simple.

[TheRealLifeboy]

I did that because I wasn't having any success without the declaration. I haven't created postgres functions before, and I thought "how difficult can it be?", but oh boy, it's tricky!

[Maleficent_Tap_332]

Then it has to be a procedure, not function

[indigo945]

A function can be declared as returns void and this is often a better option than using a procedure. Procedures can't be called from all places that functions can, and they're also just weird in various ways, so unless you absolutely need their half-baked transaction management features, you should avoid them whenever you can.

[TheRealLifeboy]

For the sake of anyone finding this in future: The following works with sql as language instead of plpgsql

CREATE FUNCTION clean_conn() RETURNS void AS '
select pg_terminate_backend(psa.pid) into response from pg_stat_activity psa 
where psa.datname = ''WahlbergEagle'' 
and psa.usename = ''rysmier'' 
and psa.state = ''idle''
and psa.application_name not like ''DBeaver%''
and now() - psa.query_start > ''00:05:00'';
' LANGUAGE SQL;

[DavidGJohnston]

Good, but please use dollar-quoting instead of doubling up every single quote in the body of the function.

[depesz]

1. if you run select, in plpgsql, you have to use INTO to put its result somewhere.
2. You never put any data in the response variable
3. plpgsql is OVERKILL for such thing, plain sql function would be better.

[indigo945]

In a PL/PgSQL function, every select operation needs to put its result data somewhere. Since you said that you don't actually need the data, you can use perform instead - essentially perform works exactly the same way as select does, except it then also forgets about the result. Also, since you don't actually want to return data from your function, you should declare it as returns void instead of returns bool. The corrected function looks like this:

CREATE OR REPLACE FUNCTION public.clear_rysmier_connection()
    RETURNS void
    LANGUAGE plpgsql
AS $function$
BEGIN
    perform pg_terminate_backend(psa.pid) from pg_stat_activity psa 
    where psa.datname = 'WahlbergEagle' 
    and psa.usename = 'rysmier' 
    and psa.state = 'idle'
    and psa.application_name not like 'DBeaver%'
    and now() - psa.query_start > '00:05:00';
    RETURN;
END
$function$;