How to create function that returns nothing

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?

Solution 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;

Solution 2:

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.

Solution 3:

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 |           |          |