159

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,976
  • 3
  • 19
  • 35
Kabi
  • 1,905
  • 5
  • 20
  • 22
  • [Please check out my answer if using PostgreSQL 11+ for a method of doing this with `PROCEDURE`s](https://stackoverflow.com/a/70076078/124486) – Evan Carroll Nov 23 '21 at 06:02

4 Answers4

247

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
  • 5,236
  • 3
  • 21
  • 26
  • 35
    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
  • 3
    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
  • @pir 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 – caleb baker Jun 24 '21 at 15:00
  • 1
    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](https://stackoverflow.com/a/22805260/11537143) – dian jin Nov 14 '22 at 04:50
13

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 |           |          |
Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
1

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.

山の平和
  • 88
  • 8
0

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

dian jin
  • 195
  • 3
  • 12