CODEWITHSUNDEEP
databasepostgresqlplpgsqlvoid
Kabi
Kabi

Reputation: 2045

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?

Upvotes: 173

Views: 160447

Answers (5)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

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;

Upvotes: 2

dian jin
dian jin

Reputation: 304

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

Upvotes: 0

Evan Carroll
Evan Carroll

Reputation: 1

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

Upvotes: 16

davcli
davcli

Reputation: 121

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.

Upvotes: 1

sqreept
sqreept

Reputation: 5534

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;

Upvotes: 276

Related Questions