Re: database session variables

Mike, Thanks heaps for your help here. I'll try that (although I'm a bit of
a freebsd/c newbie).

My system didn't have postgres.h, so I'm using postgres_fe.h instead. In
addition, I don't have fmgr.h. I downloaded version 1.18 of it from
http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/include/fmgr.h?s
ortby=date&only_with_tag=REL7_2_STABLE, but the first gcc line produced a
number of compiler errors and warnings, starting with:
fmgr.h:30: syntax error before `*'
fmgr.h:30: `Datum' declared as function returning a function

line 30 of fmgr.h is
typedef Datum (*PGFunction) (FunctionCallInfo fcinfo);

Do you know what is causing this? (gcc version:gcc-2.95.3)

Thanks in advance,
Mark

----- Original Message -----
From: "Mike Mascari" <mascarm(at)mascari(dot)com>
To: "Mark Wilson" <mark(at)mediasculpt(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Thursday, October 03, 2002 10:46 AM
Subject: Re: [GENERAL] database session variables

> Mark Wilson wrote:
> > Ok,
> >
> > Perhaps I'd better explain a bit more about what I am wanting to do with
the
> > session variables.
> >
> > I want to create a separation layer between business logic and
application
> > logic, by putting business logic in the database. To do this, all
> > application layer access will be limited to views and stored procedures.
In
> > addition, I want each database connection to be selected from a common
pool
> > (e.g., all user 'web_user'). So every database connection will be made
by
> > user 'web_user'.
>
> Again, server-side C will allow you to do what you want. You'd
> actually be setting an environmental variable in the backend and
> referencing it later...
>
> C Source:
> ---------
>
> #include "postgres.h"
> #include "fmgr.h"
>
> /* Routine to set the session id. Might want to change to
> actually authenticate the user here with a password parameter */
>
> PG_FUNCTION_INFO_V1(setwebuser);
>
> Datum setwebuser(PG_FUNCTION_ARGS) {
>
> char *buffer;
> int len;
>
> text *t = (text *) PG_GETARG_TEXT_P(0);
> len = VARSIZE(t) - VARHDRSZ;
> buffer = (char *) malloc(len + 1);
> memcpy(buffer, VARDATA(t), len);
> buffer[len] = 0;
>
> if (setenv("WEBUSER", buffer, 1) != 0) {
> free(buffer);
> elog(ERROR, "Unable to set session id");
> }
> free(buffer);
>
> PG_RETURN_INT32(1);
>
> };
>
> /* Routine to get the session webuser id */
>
> PG_FUNCTION_INFO_V1(getwebuser);
>
> Datum getwebuser(PG_FUNCTION_ARGS) {
>
> text *t;
> char *result;
> int len;
>
> result = getenv("WEBUSER");
> if (result == NULL) {
> elog(ERROR, "Session id not set");
> }
>
> len = strlen(result) + VARHDRSZ;
> t = (text *) palloc(len);
> VARATT_SIZEP(t) = len;
> memcpy(VARDATA(t), result, len - VARHDRSZ);
>
> PG_RETURN_TEXT_P(t);
>
> }
>
> Compile:
> -------
> gcc -c test.c -I/usr/include/pgsql/server
> gcc -shared -o test.so test.o
>
> Create the functions:
> --------------------
>
> CREATE OR REPLACE FUNCTION setwebuser(text) RETURNS int4
> AS '/tmp/test.so'
> LANGUAGE 'C' WITH (isStrict);
>
> CREATE OR REPLACE FUNCTION getwebuser() RETURNS text
> AS '/tmp/test.so'
> LANGUAGE 'C' WITH (isStrict);
>
> Example:
> -------
>
> create table salaries (
> key integer not null,
> salary float8 not null,
> owner text not null
> );
>
> create view v_salaries as
> select * from salaries where owner = getwebuser();
>
> insert into salaries values (1, 10000, 'Mike');
> insert into salaries values (2, 20000, 'Joe');
>
> select setwebuser('Mike');
>
> test=# select * from v_salaries;
> key | salary | owner
> -----+--------+-------
> 1 | 10000 | Mike
>
>
> HTH,
>
> Mike Mascari
> mascarm(at)mascari(dot)com
>
>
>
>
>
>