Re: Creating a session variable in Postgres

Hi Thx Mike, it's the best solution i think.

But i did some modifications to the code since i need to store an integer I
wrote the following:

#include "postgres.h"
#include "fmgr.h"

int32 session_data;

PG_FUNCTION_INFO_V1(setvalue);

Datum setvalue(PG_FUNCTION_ARGS) {

session_data = PG_GETARG_INT32(0);
PG_RETURN_BOOL(true);

}

PG_FUNCTION_INFO_V1(getvalue);
Datum getvalue(PG_FUNCTION_ARGS) {

PG_RETURN_INT32(session_data);
}

ANY COMMENTS ARE WELCOMED.

Najib.

----- Original Message -----
From: "Mike Mascari" <mascarm(at)mascari(dot)com>
To: "Nagib Abi Fadel" <nagib(dot)abi-fadel(at)usj(dot)edu(dot)lb>
Cc: "Manfred Koizar" <mkoi-pg(at)aon(dot)at>; "Bruce Momjian"
<pgman(at)candle(dot)pha(dot)pa(dot)us>; "generalpost" <pgsql-general(at)postgresql(dot)org>
Sent: Friday, June 04, 2004 11:21 AM
Subject: Re: [GENERAL] Creating a session variable in Postgres

> Nagib Abi Fadel wrote:
>
> > So considering those facts, it would be better to use the Sequence
Method,
> > since it would only require cleaning up one table ....
> > Or is there anything else i am missing ???
>
> It is becoming more of a toss-op. Prior to 7.4, the system indexes
> would grow until a manual REINDEX was issued in a stand-alone
> backend. In 7.4, the dead tuples remain, but at least can be re-used
> once they've been marked that way by the occassional vacuum.
> autovacuum will tend to make dead-tuple reclaimation transparent,
> like Oracle.
>
> The absolutely cheapest method is to write a pair of functions in
> 'C' that sets/gets a global variable:
>
> #include "postgres.h"
> #include "fmgr.h"
>
> #define MAX_DATA 64
>
> char session_data[MAX_DATA] = "";
>
> PG_FUNCTION_INFO_V1(setvalue);
>
> Datum setvalue(PG_FUNCTION_ARGS) {
>
> text *value;
> long len;
>
> value = PG_GETARG_TEXT_P(0);
> len = VARSIZE(value) - VARHDRSZ;
> if (len >= MAX_DATA) {
> elog(ERROR, "setvalue: value too long: %li", len);
> }
> memcpy(session_data, VARDATA(value), len);
> session_data[len] = 0;
>
> PG_RETURN_BOOL(true);
>
> }
>
> PG_FUNCTION_INFO_V1(getvalue);
>
> Datum getvalue(PG_FUNCTION_ARGS) {
>
> text *result;
> long len;
>
> len = strlen(session_data) + VARHDRSZ;
> result = (text *) palloc(len);
> VARATT_SIZEP(result) = len;
> memcpy(VARDATA(result), session_data, len - VARHDRSZ);
>
> PG_RETURN_TEXT_P(result);
>
> }
>
> -- Compile
>
> gcc -c example.c -I/usr/include/pgsql/server
> gcc -shared -o pgexample.so pgexample.o
>
> -- Install somewhere PostgreSQL can get at it
>
> cp pgexample.so /usr/local/mypglibs
>
> -- Create the functions where path-to-lib is the path to
> -- the shared library.
>
> CREATE OR REPLACE FUNCTION setvalue(text) RETURNS boolean
> AS '/usr/local/mypglibs/pgexample.so'
> LANGUAGE 'C' WITH (isStrict);
>
> CREATE OR REPLACE FUNCTION getvalue() RETURNS text
> AS '/usr/local/mypglibs/pgexample.so'
> LANGUAGE 'C' WITH (isStrict);
>
> Now all you need to to is invoke setvalue() at the start of the
> session, and build views around getvalue():
>
> CREATE VIEW v_foo AS
> SELECT *
> FROM foo
> WHERE foo.key = getvalue();
>
> At the start of a session:
>
> SELECT setvalue('Mike Mascari was here');
>
> Hope that helps,
>
> Mike Mascari
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html