By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,852 Members | 2,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,852 IT Pros & Developers. It's quick & easy.

Changing user

P: n/a
C G
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"C G" <cs******@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.