Thread: Create User
Hi,
I have this table:
CREATE TABLE users
(
userid varchar(100) NOT NULL,
nama varchar(50) NOT NULL,
pword varchar(255) NOT NULL,
groupe varchar(7) NOT NULL,
rolle int2 NOT NULL DEFAULT 2,
statux varchar(9) NOT NULL DEFAULT 'Active'::character varying,
CONSTRAINT users_pkey PRIMARY KEY (userid)
)
WITHOUT OIDS;
I created a trigger to create a user based on the new insert into the
table as follows:
CREATE OR REPLACE FUNCTION users_insert()
RETURNS "trigger" AS
$BODY$
BEGIN
CREATE USER NEW.userid WITH PASSWORD NEW.pword IN GROUP NEW.groupe;
RETURN new;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Surprisingly, I get this error message:
ERROR: syntax error at or near "$1" at character 14
QUERY: CREATE USER $1 WITH PASSWORD $2 IN GROUP $3
CONTEXT: SQL statement in PL/PgSQL function "users_insert" near line 10
I would appreciate your guidance.
Cheers.
Chris.
___________________________________________________________
Yahoo! Photos NEW, now offering a quality print service from just 8p a photo http://uk.photos.yahoo.com
I hope this error arises when you do a insert. Can u post your insert statement that caused this error? On 1/20/06, DB Subscriptions <db.subscriptions@shepherdhill.biz> wrote: > Hi, > > I have this table: > > CREATE TABLE users > ( > userid varchar(100) NOT NULL, > nama varchar(50) NOT NULL, > pword varchar(255) NOT NULL, > groupe varchar(7) NOT NULL, > rolle int2 NOT NULL DEFAULT 2, > statux varchar(9) NOT NULL DEFAULT 'Active'::character varying, > CONSTRAINT users_pkey PRIMARY KEY (userid) > ) > WITHOUT OIDS; > > I created a trigger to create a user based on the new insert into the > table as follows: > > CREATE OR REPLACE FUNCTION users_insert() > RETURNS "trigger" AS > $BODY$ > BEGIN > CREATE USER NEW.userid WITH PASSWORD NEW.pword IN GROUP NEW.groupe; > > RETURN new; > END; > > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > > Surprisingly, I get this error message: > > ERROR: syntax error at or near "$1" at character 14 > QUERY: CREATE USER $1 WITH PASSWORD $2 IN GROUP $3 > CONTEXT: SQL statement in PL/PgSQL function "users_insert" near line 10 > > I would appreciate your guidance. > > Cheers. > > Chris. > > > > ___________________________________________________________ > Yahoo! Photos – NEW, now offering a quality print service from just 8p a photo http://uk.photos.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >
Thanks Pandurangan. The Function could not even be saved or created. The error is not at the insert level but at the creation of the trigger function. Regards. Pandurangan R S wrote: >I hope this error arises when you do a insert. >Can u post your insert statement that caused this error? > >On 1/20/06, DB Subscriptions <db.subscriptions@shepherdhill.biz> wrote: > > >>Hi, >> >>I have this table: >> >>CREATE TABLE users >>( >> userid varchar(100) NOT NULL, >> nama varchar(50) NOT NULL, >> pword varchar(255) NOT NULL, >> groupe varchar(7) NOT NULL, >> rolle int2 NOT NULL DEFAULT 2, >> statux varchar(9) NOT NULL DEFAULT 'Active'::character varying, >> CONSTRAINT users_pkey PRIMARY KEY (userid) >>) >>WITHOUT OIDS; >> >>I created a trigger to create a user based on the new insert into the >>table as follows: >> >>CREATE OR REPLACE FUNCTION users_insert() >> RETURNS "trigger" AS >>$BODY$ >>BEGIN >> CREATE USER NEW.userid WITH PASSWORD NEW.pword IN GROUP NEW.groupe; >> >> RETURN new; >>END; >> >>$BODY$ >> LANGUAGE 'plpgsql' VOLATILE; >> >>Surprisingly, I get this error message: >> >>ERROR: syntax error at or near "$1" at character 14 >>QUERY: CREATE USER $1 WITH PASSWORD $2 IN GROUP $3 >>CONTEXT: SQL statement in PL/PgSQL function "users_insert" near line 10 >> >>I would appreciate your guidance. >> >>Cheers. >> >>Chris. >> >> >> >>___________________________________________________________ >>Yahoo! Photos – NEW, now offering a quality print service from just 8p a photo http://uk.photos.yahoo.com >> >>---------------------------(end of broadcast)--------------------------- >>TIP 4: Have you searched our list archives? >> >> http://archives.postgresql.org >> >> >> ___________________________________________________________ To help you stay safe and secure online, we've developed the all new Yahoo! Security Centre. http://uk.security.yahoo.com
On Fri, 2006-01-20 at 09:16 +0100, DB Subscriptions wrote: > BEGIN > CREATE USER NEW.userid WITH PASSWORD NEW.pword IN GROUP NEW.groupe; > > RETURN new; > END; You can't use PL/PgSQL variables in DDL commands. Try using EXECUTE: EXECUTE 'CREATE USER ' || NEW.userid || '...'; -Neil