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?

Search Discussions

  • Dennis Brakhane at Jan 7, 2008 at 3:02 pm
    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?

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJan 7, '08 at 1:37p
activeJan 7, '08 at 3:02p
posts2
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase