Thread: Create User

Create User

From
DB Subscriptions
Date:
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

Re: Create User

From
Pandurangan R S
Date:
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
>

Re: Create User

From
DB Subscriptions
Date:
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

Re: Create User

From
Neil Conway
Date:
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