wiredfool

(Postgre)Sql is type safe. Sql has transactions. When you do something in a function, it should all complete, or none, and the type system should catche a lot of these errors before they bite you.

Until it doesn’t.

Spot the Bug: (apart from going a very long way around to set two columns of a table the same, assume that do_stuff has some other interesting effects)

create or replace function do_stuff(bigint) returns bigint as '
update tbl set other_id=$1 where id=$1;
select $id;
 ' language 'sql';

create or replace function foo() returns bigint as '
insert into tbl (id, other_id) 
  select id, null from transactions where id in (1,2,3);
select do_stuff(id) from transactions where id in (1,2,3);
' language 'sql';

There are really 2 bugs — one is that a select statement is doing non-restian stuff by changing state in the system. It’s a somewhat accepted practice, but it leads to other errors, namely:

This is specified as returning a bigint, yet the result of the last select in foo() can return a set. Since the return type rules, what you end up getting is the same as appending limit 1 to the end of the last query.

So, at least in postgresql, you’d end up wiht a table that looked like:

1 | 1
2 | null
3 | null

instead of the expected:

1 | 1
2 | 2
3 | 3

And that can make you sit and stare really hard at a function until none of it makes sense. The answer, apart from ‘don’t do that’ is to make the function return setof bigint, making it clear that what you’re getting out is possibly going to be more than just a single number.