Thread: A way of storing variables - will this work?

Hello,

For a project I am working on I needed some way of storing a variable for
the duration of a session and cooked this up, based on some previous posts
to this list:


create sequence variable_id_seq;

create table variables (
       id     integer
         primary key
         default nextval('variable_id_seq'),
       value integer
);

create function set_var(integer)
returns integer
as 'insert into variables (value) values ($1);
    select $1 as variable;'
language 'sql';

create function get_var()
returns integer
as 'select value
    from variables
    where id = currval(''variable_id_seq'');'
language 'sql';


Basically you set the variable using set_var(1234) and then get it back
using get_var().

I have tested this in as mawy ways as I can think of, but is there
any reason why it should not work? It will be used to store the access
level of the user and so to implement a security policy inside the
database.

Many thanks,

Edmund.

--
 ***********************************************************
 *** Edmund von der Burg ***   edmund@ecclestoad.co.uk   ***
 ***    Eccles & Toad    *** http://www.ecclestoad.co.uk ***
 ***********************************************************

On Wed, 27 Jun 2001, Edmund von der Burg wrote:

> Hello,
>
> For a project I am working on I needed some way of storing a variable for
> the duration of a session and cooked this up, based on some previous posts
> to this list:
>
>
> create sequence variable_id_seq;
>
> create table variables (
>        id     integer
>          primary key
>          default nextval('variable_id_seq'),
>        value integer
> );
>
> create function set_var(integer)
> returns integer
> as 'insert into variables (value) values ($1);
>     select $1 as variable;'
> language 'sql';
>
> create function get_var()
> returns integer
> as 'select value
>     from variables
>     where id = currval(''variable_id_seq'');'
> language 'sql';
>
>
> Basically you set the variable using set_var(1234) and then get it back
> using get_var().
>
> I have tested this in as mawy ways as I can think of, but is there
> any reason why it should not work? It will be used to store the access
> level of the user and so to implement a security policy inside the
> database.

If everything runs inside a transaction, there is no issue with regard to concurrency and getting th ecorrect value
withcurrval() through get_var(). 

cheers,
thalis

>
> Many thanks,
>
> Edmund.
>
> --
>  ***********************************************************
>  *** Edmund von der Burg ***   edmund@ecclestoad.co.uk   ***
>  ***    Eccles & Toad    *** http://www.ecclestoad.co.uk ***
>  ***********************************************************
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>