Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010
    Posts
    2

    Unanswered: Can Plpgsql functions retain state?

    Can plpgsql functions retain state? I'd like a variable I set earlier to still be set the same the next time I execute it.

    I'm using 'PostgreSQL 8.4.3, compiled by Visual C++ build 1400, 32-bit'.

    Thanks,
    Daniel

    ps. A related question: My understanding is that PL/pgSQL, unlike Oracle's PL/SQL, translates everything to SQL before execution (this understanding could be very wrong...?). Does this mean that there is no benefit to "avoiding engine changes" in PL/pgSQL? In PL/SQL we would avoid calling a SQL statement if possible. If PL/pgSQL statements are always translated to SQL does that I might as well just use a temporary table if I want to "retain state"? thanks again... /D
    Last edited by DanielH; 04-19-10 at 13:00. Reason: I misused the word "reentrant". I meant to "retain state"

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    You need to define the context. Retain state across multiple connections, for a single session, within a query... But functions created in c can retain state. In particular check out set returning functions (SRF) in c.

    The distinction between PL/SQL and SQL in Oracle can be a real PITB. In Postgres, the transition is much smoother, but it still exists. PL/pgSQL functions do NOT get translated directly to SQL. If you want the best performance try to write your functions in sql instead of plpgsql. Many times sql functions can be in-lined.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by DanielH View Post
    Can plpgsql functions retain state? I'd like a variable I set earlier to still be set the same the next time I execute it.

    I'm using 'PostgreSQL 8.4.3, compiled by Visual C++ build 1400, 32-bit'.

    Thanks,
    Daniel
    If you only have a limited number of variables, you might be able to use so called "customized options". They are intended for configuration options of add-on modules, but can be used "outside" of an add-on as well:

    PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Customized Options
    PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: System Administration Functions

    Once the variables are defined in postgresql.conf (requires a restart!), you can query the variable
    Code:
    select current_setting('myvars.value_1');
    and you can also define a new value that can either be global or "local" to the current connection
    Code:
    select set_config('myvars.value_1', 'new_value', true);
    If you are using a connection pool, that won't obviously work.

    Another solution (outside of C) could be to define a "session" table that contains a row for each session with two columns. One identifying the session the other one being a hstore column which is essentially a key/value map that can contain any number of keys.

    If the table is small enough and accessed very often it will most probably reside in the cache (either Postgres buffer or filesystem cache), so access to it should be quite fast.

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    IMO customized options are generally more trouble than they are worth.

    For persistent variables we typically use a variables table with a few columns to define access (global, user, session) the name of the variable and the value along with some getter/setter functions. But I like your hstore idea.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •