Create User

I hope this error arises when you do a insert.
Can u post your insert statement that caused this error?

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