How to create function that returns nothing

PostgresqlFunctionPlpgsqlVoid

Postgresql Problem Overview


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?

Postgresql Solutions


Solution 1 - Postgresql

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

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

Solution 3 - Postgresql

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.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionKabiView Question on Stackoverflow
Solution 1 - PostgresqlsqreeptView Answer on Stackoverflow
Solution 2 - PostgresqlEvan CarrollView Answer on Stackoverflow
Solution 3 - PostgresqlDakliView Answer on Stackoverflow