Re: database session variables - Mailing list pgsql-general

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@mascari.com>
To: "Mark Wilson" <mark@mediasculpt.com>
Cc: <pgsql-general@postgresql.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@mascari.com
>
>
>
>
>
>