From: | Keresztury Balázs <balazs(at)gaslightmusic(dot)hu> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | create role in a pl/pgsql trigger |
Date: | 2010-01-26 17:09:43 |
Message-ID: | 002601ca9eaa$5ba37b70$12ea7250$@hu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-01-26 17:14:06 | Re: pg dump.. issue with when using crontab |
Previous Message | Andreas Kretschmer | 2010-01-26 17:07:15 | Re: pg dump.. issue with when using crontab |