13

How do I write a simple stored procedure in postgres that doesn't return a value at all? Even with a void return type when I call the stored procedure I get a single row back.

CREATE FUNCTION somefunc(in_id bigint) RETURNS void AS $$
BEGIN
   DELETE from test_table where id = in_id;
END;
$$ LANGUAGE plpgsql;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
ScArcher2
  • 85,501
  • 44
  • 121
  • 160

3 Answers3

8

It's not the function that returns value, it's the SELECT you used to call it. If it doesn't return any rows, it doesn't run your function.

Michael Krelin - hacker
  • 138,757
  • 24
  • 193
  • 173
6

You can achieve "nothing returned" by abusing set-returning functions:

Simple function:

create function x () returns setof record as $$
begin
return;
END;
$$ language plpgsql;

Now you can:

# select x();
 x
---
(0 rows)

In case it doesn't work for you (sorry, I'm using 8.5), try with this approach:

# create function x (OUT o1 bool, OUT o2 bool) returns setof record as $$
begin
return;
END;
$$ language plpgsql;
CREATE FUNCTION

The parameters are irrelevant, but:

  • You need > 1 of them
  • They have to be named

And now you can:

# select * from x();
 o1 | o2
----+----
(0 rows)
Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
5

You are doing just fine. You dont need to add anything else.

The result of the row is null, so it is a void return.

I don't think theres something you can do about that. Checking my void functions all of them are just like yours.

returns void as $$ and no return statement in code block.

George Silva
  • 3,454
  • 10
  • 39
  • 64