Re: Creating a session variable in Postgres - Mailing list pgsql-general

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