Thread: Changing user

Dear All,

I have a simple table
CREATE TABLE table1{
user name,
si numeric
};

Also I have function:
CREATE FUNCTION check1(numeric) RETURN text AS'
DECLARE
SI ALIAS AS $1;
username name;

BEGIN
username:=(SELECT user FROM table1 WHERE si=SI)
SET SESSION AUTHORIZATION username;
RETURN username;
END;
'LANGUAGE 'plpgsql';

However, when I call the function I get the error:
ERROR: syntax error at or near "$1" at character 28
CONTEXT: PL/pgSQL function "check1" line11 at SQL statement

If I comment out the "SET SESSION ..." the function works, i.e. it outputs a
valid username. I create and execute the function as a superuser.

Any suggestions?

Many thanks

Colin

_________________________________________________________________
Hotmail messages direct to your mobile phone http://www.msn.co.uk/msnmobile

"C G" <csgcsg39@hotmail.com> writes:
> SET SESSION AUTHORIZATION username;
> ERROR: syntax error at or near "$1" at character 28

You'll need to use EXECUTE to do this.  Utility statements in general
aren't prepared to deal with parameters.

            regards, tom lane

Thanks for your help but I still have a small problem. I'm try to do as you
suggested and use prepare/execute but I'm doing something silly.

I'm using:

PREPARE my_prep1(name) AS SET SESSION AUTHORIZATION $1;

and get the error message:
ERROR: syntax error at or near "set" at character 27

I have tried many variations on this theme but have no luck. Suggestions?

Thanks

Colin




>"C G" <csgcsg39@hotmail.com> writes:
> > SET SESSION AUTHORIZATION username;
> > ERROR: syntax error at or near "$1" at character 28
>
>You'll need to use EXECUTE to do this.  Utility statements in general
>aren't prepared to deal with parameters.
>
>            regards, tom lane

_________________________________________________________________
Express yourself with cool emoticons - download MSN Messenger today!
http://www.msn.co.uk/messenger

On Tuesday 16 December 2003 10:12, C G wrote:
> Thanks for your help but I still have a small problem. I'm try to do as you
> suggested and use prepare/execute but I'm doing something silly.
>
> I'm using:
>
> PREPARE my_prep1(name) AS SET SESSION AUTHORIZATION $1;
>
> and get the error message:
> ERROR: syntax error at or near "set" at character 27

You want EXECUTE. Something like:

DECLARE
  set_qty text;
...
set_qry := ''SET SESSION AUTHORIZATION '' || $1;
EXECUTE set_qry;


--
  Richard Huxton
  Archonet Ltd