123

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?

lospejos
  • 1,888
  • 3
  • 17
  • 31
Kabi
  • 1,525
  • 5
  • 17
  • 21

2 Answers2

197

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;
sqreept
  • 4,646
  • 3
  • 18
  • 26
  • 22
    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. – Craig Ringer Jan 08 '13 at 14:17
  • Here's a related case making use of `#variable_conflict`: http://dba.stackexchange.com/a/105828/3684 – Erwin Brandstetter Jul 03 '15 at 19:36
  • 2
    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`. – pir Dec 22 '18 at 18:28
0

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.

Dakli
  • 70
  • 7