Hi,
I have this table:
CREATE TABLE users
(
userid varchar(100) NT NULL,
nama varchar(50) NT NULL,
pword varchar(255) NT NULL,
groupe varchar(7) NT NULL,
rolle int2 NT NULL DEFAULT 2,
statux varchar(9) NT NULL DEFAULT 'Active'::character varying,
CNSTRAINT users_pkey PRIMARY KEY (userid)
)
WITHUT IDS;
I created a trigger to create a user based on the new insert into the
table as follows:
CREATE R REPLACE FUNCTIN users_insert()
RETURNS "trigger" AS
$BDY$
BEGIN
CREATE USER NEW.userid WITH PASSWRD NEW.pword IN GRUP NEW.groupe;
RETURN new;
END;
$BDY$
LANGUAGE 'plpgsql' VLATILE;
Surprisingly, I get this error message:
ERRR: syntax error at or near "$1" at character 14
QUERY: CREATE USER $1 WITH PASSWRD $2 IN GRUP $3
CNTEXT: 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

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

1/20/06, DB Subscriptions <db.subscriptions (AT) shepherdhill (DOT) bizwrote:
Hi,

I have this table:

CREATE TABLE users
(
userid varchar(100) NT NULL,
nama varchar(50) NT NULL,
pword varchar(255) NT NULL,
groupe varchar(7) NT NULL,
rolle int2 NT NULL DEFAULT 2,
statux varchar(9) NT NULL DEFAULT 'Active'::character varying,
CNSTRAINT users_pkey PRIMARY KEY (userid)
)
WITHUT IDS;

I created a trigger to create a user based on the new insert into the
table as follows:

CREATE R REPLACE FUNCTIN users_insert()
RETURNS "trigger" AS
$BDY$
BEGIN
CREATE USER NEW.userid WITH PASSWRD NEW.pword IN GRUP NEW.groupe;

RETURN new;
END;

$BDY$
LANGUAGE 'plpgsql' VLATILE;

Surprisingly, I get this error message:

ERRR: syntax error at or near "$1" at character 14
QUERY: CREATE USER $1 WITH PASSWRD $2 IN GRUP $3
CNTEXT: 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

(end of broadcast)
TIP 2: Don't 'kill -9' the postmaster

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?
>
1/20/06, DB Subscriptions <db.subscriptions (AT) shepherdhill (DOT) bizwrote:

>
>>Hi,
>>
>>I have this table:
>>
>>CREATE TABLE users
>>(
>userid varchar(100) NT NULL,
>nama varchar(50) NT NULL,
>pword varchar(255) NT NULL,
>groupe varchar(7) NT NULL,
>rolle int2 NT NULL DEFAULT 2,
>statux varchar(9) NT NULL DEFAULT 'Active'::character varying,
>CNSTRAINT users_pkey PRIMARY KEY (userid)
>>)
>>WITHUT IDS;
>>
>>I created a trigger to create a user based on the new insert into the
>>table as follows:
>>
>>CREATE R REPLACE FUNCTIN users_insert()
>RETURNS "trigger" AS
>>$BDY$
>>BEGIN
>CREATE USER NEW.userid WITH PASSWRD NEW.pword IN GRUP NEW.groupe;
>>
>RETURN new;
>>END;
>>
>>$BDY$
>LANGUAGE 'plpgsql' VLATILE;
>>
>>Surprisingly, I get this error message:
>>
>>ERRR: syntax error at or near "$1" at character 14
>>QUERY: CREATE USER $1 WITH PASSWRD $2 IN GRUP $3
>>CNTEXT: 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

(end of broadcast)
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Fri, 2006-01-20 at 09:16 +0100, DB Subscriptions wrote:
BEGIN
CREATE USER NEW.userid WITH PASSWRD NEW.pword IN GRUP NEW.groupe;

RETURN new;
END;

You can't use PL/PgSQL variables in DDL commands. Try using EXECUTE:

EXECUTE 'CREATE USER ' || NEW.userid || '';
-Neil

(end of broadcast)
TIP 2: Don't 'kill -9' the postmaster