Re: PostgreSQL sequence within function

Russ Brown <pickscrape(at)gmail(dot)com> writes:
> This just made me think. If I was writing this function, I would have
> written it as an SQL function like this:

> CREATE or REPLACE FUNCTION getSeq() RETURNS int AS $$
> SELECT nextval('myseq');
> $$ LANGUAGE SQL;

> Does anybody know which version is actually better/faster/more optimal?

In recent releases the SQL version would be better, since it would
actually get "inlined" into the calling query and thus the function
overhead would be zero. However this only happens for a fairly narrow
set of cases (function returning scalar, not set, and there are
constraints as to strictness and volatility properties). A non-inlined
SQL function is probably slower than plpgsql, because the SQL-function
executor code isn't amazingly efficient (doesn't cache query plans from
one use to the next, for instance).

So the short answer is "it depends".

regards, tom lane