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@mascari.com>
To: "Nagib Abi Fadel" <nagib.abi-fadel@usj.edu.lb>
Cc: "Manfred Koizar" <mkoi-pg@aon.at>; "Bruce Momjian"
<pgman@candle.pha.pa.us>; "generalpost" <pgsql-general@postgresql.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