Thread: problem with automatic altering of groups

"Matthias Nagl" <mnagl@web.de> schrieb am 20.08.03 23:09:03: 
Hello,   
is ist possible to write a function that alters the group of a given user like the following should do in my  
opinion (I'd like to use it in a rule):   
CREATE FUNCTION set_group (text, integer) RETURNS text AS '  ALTER GROUP userlevel1 DROP USER $1; ALTER GROUP
userlevel2DROP USER $1; ALTER GROUP  
 
userlevel3 DROP USER $1;  ALTER GROUP userlevel4 DROP USER $1; ALTER GROUP userlevel5 DROP USER $1;  CASE $2      WHEN
5THEN          (ALTER GROUP userlevel5 ADD USER $1)      WHEN 4 THEN          (ALTER GROUP userlevel5 ADD USER $1;
ALTERGROUP userlevel4 ADD USER $1;)      [...]  END;  SELECT '';  
 
' LANGUAGE 'SQL';   
I don't understand why this function ist refused by postgresql with an parse error at the first $1 as ist every  
other method I tryed to get postgres making an "alter group" with a variable. I'd be very happy if someone  
could help...   
yours   
Matthias Nagl  
__________________________________________________________________________
Die sicherste Form der Kommunikation: E-Mails verschluesseln, Spam-Filter,
Adressverifizierung, digitale Unterschrift: http://freemail.web.de

Matthias,

> I don't understand why this function ist refused by postgresql with an parse
error at the first $1 as ist every
> other method I tryed to get postgres making an "alter group" with a
variable. I'd be very happy if someone
> could help...

You can't substitute variables for object names.   If you need to construct
dynamic query strings, use PL/pgSQL and EXECUTE:

sql_qry := ''ALTER GROUP '' || $group || '' ADD USER '' || $user;
EXECUTE sql_query;


--
-Josh BerkusAglio Database SolutionsSan Francisco