Thread: create role in a pl/pgsql trigger

hi,

I would like to write a trigger on a table which contains detailed
information about users. If I add a new user to this table, I'd like this
trigger to add the user to the database as well. Later I also want to
implement triggers for updating and deleting, but I got stuck at the first
part of this task.

Everything went fine until the point I had to insert the username into the
create role command. Appearently the command takes only parameters without
the ' signs, and the language supports only substituting parameters with the
apostrophe.

Any idea?

Thanks,
Balazs



Relevant metadata:

CREATE TABLE "felhasznalo"."felhasznalo" (
  "felhasznalo_id" VARCHAR NOT NULL,
  "vezeteknev" VARCHAR,
  "keresztnev" VARCHAR,
  "utolso_belepes" TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
  "kell_uj_jelszo" BOOLEAN DEFAULT false NOT NULL,
  "aktiv" BOOLEAN DEFAULT true NOT NULL,
  "aktiv_mettol" TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
  "aktiv_meddig" TIMESTAMP WITHOUT TIME ZONE,
  "modosito" VARCHAR DEFAULT "current_user"(),
  "modositas_idopont" TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
  CONSTRAINT "felhasznalo_pkey" PRIMARY KEY("felhasznalo_id")
) WITH OIDS;

CREATE TRIGGER "felhasznalo_letrehozas" BEFORE INSERT
ON "felhasznalo"."felhasznalo" FOR EACH ROW
EXECUTE PROCEDURE "public"."felhasznalo_letrehozas_trigger"();

CREATE OR REPLACE FUNCTION "public"."felhasznalo_letrehozas_trigger" (
)
RETURNS trigger AS
$body$
BEGIN
    CREATE ROLE NEW.felhasznalo_id INHERIT LOGIN IN ROLE "USR" ENCRYPTED
PASSWORD NEW.felhasznalo_id;
      RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

On 27/01/2010 1:09 AM, Keresztury Balázs wrote:
> hi,
>
> I would like to write a trigger on a table which contains detailed
> information about users. If I add a new user to this table, I'd like this
> trigger to add the user to the database as well. Later I also want to
> implement triggers for updating and deleting, but I got stuck at the first
> part of this task.
>
> Everything went fine until the point I had to insert the username into the
> create role command. Appearently the command takes only parameters without
> the ' signs, and the language supports only substituting parameters with the
> apostrophe.
>
> Any idea?

Use 'EXECUTE' (without "USING" because "EXECUTE ... USING" doesn't work
for utility statements).

--
Craig Ringer

Thanks Craig, this one worked!

Balazs

-----Original Message-----
From: Craig Ringer [mailto:craig@postnewspapers.com.au]
Sent: Wednesday, January 27, 2010 12:34 AM
To: Keresztury Balázs
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] create role in a pl/pgsql trigger

On 27/01/2010 1:09 AM, Keresztury Balázs wrote:
> hi,
>
> I would like to write a trigger on a table which contains detailed
> information about users. If I add a new user to this table, I'd like this
> trigger to add the user to the database as well. Later I also want to
> implement triggers for updating and deleting, but I got stuck at the first
> part of this task.
>
> Everything went fine until the point I had to insert the username into the
> create role command. Appearently the command takes only parameters without
> the ' signs, and the language supports only substituting parameters with
the
> apostrophe.
>
> Any idea?

Use 'EXECUTE' (without "USING" because "EXECUTE ... USING" doesn't work
for utility statements).

--
Craig Ringer