A way of storing a variable during a session. - Mailing list pgsql-general

I have often wanted a facility in PostgreSQL which would allow me to do
something like:

SET my_variable = 1234;

and then be able to access that variable for the rest of the session
without mucking about with sequences or temporary tables. I have cooked up
something that appears to have a similar effect allowing me to store the
variable with:

SELECT stored_variable(1234);

and then call it back with:

SELECT stored_variable(0);

This works for me but I would like your comments on it before I start
using it too extensively. I have deliberately made it write once read
many during each session as I want to use the variable in creating
restricted views, the variable effectively being the access level.

The variable will be set up when the database connection is established
and will then be called repeatedly.

There may be a nicer, cleaner and less invasive way to do all of this, in
which case I would like to hear about it. I hope that the function as
presented proves helpful to some.

Yours,

Edmund von der Burg
Hall School Wimbledon.



The file 'stored_variable.c' which contains the function and associated
bumpf:


/*     stored_variable.c (author: edmund.vonderburg@hallschoolwimbledon.co.uk)

    A simple attempt to create a way to store variables in PostgreSQL.

    To use this function call it with a non-zero argument to set the value.
    To read out the value that is set call the function with zero as the
    argument.

    Add the function to your database with the following SQL:

    create function stored_variable (int) returns int
    as '/path/to/file/stored_variable.so'
    language 'C';

    where stored_variable.so was created by running gcc as so:

    gcc -shared stored_variable.c -o stored_variable.so

    This is my first attempt at the code - please comment on it to me if you
    wish.
*/

int stored_variable (int);

int stored_variable ( int incoming ) {
    // store holds the value between calls;
    static int store = 0;

    // has_been_set prevents the variable being changed.
    static int has_been_set = 0;

    // Set the variable on the first call only.
    if (has_been_set == 0) {
        store = incoming;
        has_been_set = 1;
    }

    return store;
}