Hello,
I need to write a stored procedure that does look something like this:
CREATE FUNCTION foo() RETURNS SETOF BIGINT LANGUAGE plpgsql AS $$
DECLARE
result RECORD;
BEGIN
EXECUTE 'CREATE TEMP TABLE foo (id int,...)';
-- Insert something into foo depending on contents of the database
...
FOR result IN EXECUTE 'SELECT * FROM foo' LOOP
RETURN NEXT result;
END LOOP;
EXECUTE 'DROP TABLE foo';
$$
In the documentation for CREATE FUNCTION, you will find the following
about the STABLE attribute:
>STABLE indicates that the function cannot modify the database, and
that within a single table scan
> it will consistently return the same result for the same argument values, but that its result could
> change across SQL statements.
Does that mean that the function cannot modify the database, or cannot
change the data in it?
Because the function cleary modified the database, but only
temporarily, and the result only changes when the dataset changes.
Does STABLE only tell the query optimizer that he can optimize two
simultanous calls away or does it imply some kind of write lock under
special circumstances?
To make a long question short: can I use STABLE or will I face problems?