How to create function that returns nothing

Question

I want to write a function with pl/pgsql. I'm using PostgresEnterprise Manager v3 and using shell to make a function, but in the shell I must define return type. If I don't define the return type, I'm not able to create a function.

How can create a function without return result, i.e a Function that creates a new table?

Bread

Please check out my answer if using PostgreSQL 11+ for a method of doing this with PROCEDUREs – Gretchengrete

Answer 1

Use RETURNS void like below:

CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
    #variable_conflict use_variable
    DECLARE
        curtime timestamp := now();
    BEGIN
        UPDATE users SET last_modified = curtime, comment = comment
          WHERE users.id = id;
    END;
$$ LANGUAGE plpgsql;

Neper

For other readers, note that the #variable_conflict directive has nothing to do with the rest of the answer. It's just part of an example function; the only important bit is the RETURNS void. Also, cool, I didn't know PL/PgSQL had pragmas. – Possession

Here's a related case making use of #variable_conflict: dba.stackexchange.com/a/105828/3684 – Twofold

How do I make use of this function inside of another function? If I try without SELECT * FROM stamp_user(...), then I get error: query has no destination for result data and if I just write stamp_user(...) then I get syntax error. – Villainy

@Villainy What you are saying doesn't make total sense. However, If you don't want to do anything with the SELECT data use PERFROM. If you do. you will want to SELECT * INTO _record Where _record is defined with DECLARE _record RECORD; before the BEGIN statement – Chigger

Late to the question, but replying to @pir's comment and to future readers for clarity's sake, there was a misunderstanding in the comment's question and response. When invoking your function, If you see the error query has no destination for result data, you are most probably using SELECT IN YOUR FUNCTION. So replace the SELECT with PERFORM in your function, and then call your function as per normal using SELECT yourFunction(). PERFORM is only valid in pl/pgsql context. See link – Actuality

Answer 2

PostgreSQL 11+: PROCEDUREs

PostgreSQL 11 introduces PROCEDUREs which are basically functions that return nothing, but called with CALL rather than SELECT,

How can create a function without return result, i.e a Function that creates a new table?

Like this,

=# CREATE PROCEDURE create_table_foo()
AS $$
  CREATE TABLE foo ( id int )
$$ LANGUAGE sql;

=# CALL create_table_foo();


=# \d foo;
                Table "public.foo"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 

Gretchengrete

Answer 3

Functions must always return something, although you can use procedures like

do $$

and start with normal function like

declare
...

but if you still want to do a function just add void after returns.

Weingarten

Answer 4

You can create the PL/pgSQL function which returns nothing with VOID type and with or without RETURN; as shown below. *Be careful, if you return a value with e.g. RETURN 2; for VOID type, then you get the error:

CREATE FUNCTION my_func() RETURNS VOID AS $$
BEGIN                           -- ↑ Here
RETURN; -- Here
END;
$$ LANGUAGE plpgsql;

Or:

CREATE FUNCTION my_func() RETURNS VOID AS $$
BEGIN                           -- ↑ Here
-- RETURN; -- Here
END;
$$ LANGUAGE plpgsql;

In addition, you can create the SQL function which returns nothing with VOID type as shown below. *A SQL function cannot have RETURN statement:

CREATE FUNCTION my_func() RETURNS VOID AS $$
$$ LANGUAGE SQL;

Billibilliard

Answer 5

Adding onto the accepted answer, if you see the error query has no destination for result data when invoking your function, you are most probably using SELECT in YOUR FUNCTION in one of your statements. Replace the SELECT with PERFORM, and then call your function as per normal using SELECT yourFunction().

Note: PERFORM is only valid in pl/pgsql context. See link

Actuality