From: Michael Monnerie <michael.monnerie@is.it-management.at>
Organization: it-management http://it-management.at
To: pgsql-admin@postgresql.org
Subject: Question on rule
Date: Sat, 21 Feb 2009 09:43:35 +0100
User-Agent: KMail/1.10.3 (Linux/2.6.27.13-ZMI; KDE/4.1.3; x86_64; ; )
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart2321333.nZD1C8iBUU";
	protocol="application/pgp-signature"; micalg=pgp-sha1
Content-Transfer-Encoding: 7bit
Message-Id: <200902210943.35832@zmi.at>

--nextPart2321333.nZD1C8iBUU
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

I have a question to creating a rule:

CREATE RULE dbmail_mailboxes_autocreate AS ON INSERT TO dbmail_users DO=20
ALSO insert into dbmail_mailboxes (owner_idnr,name) values=20
(NEW.user_idnr,'INBOX'),  (NEW.user_idnr,'Gesendete Objekte'),=20
(NEW.user_idnr,'Entw=C3=BCrfe'), (NEW.user_idnr,'Junk'),=20
(NEW.user_idnr,'Papierkorb');
ERROR:  VALUES must not contain OLD or NEW references
TIP:  Use SELECT ... UNION ALL ... instead.

But in http://www.postgresql.org/docs/8.3/interactive/rules-update.html=20
the examples also use NEW in the values. Or can I only use a rule within=20
the same table?

If I need to use a TRIGGER: I tried with creating a function, but I=20
don't know how to pass the current user_idnr to that function from the=20
trigger. I managed to create the function:

CREATE FUNCTION dbmail_mailboxes_autocreate_function(user_idnr bigint)=20
returns integer AS $$ BEGIN insert into dbmail_mailboxes=20
(owner_idnr,name) values (user_idnr,'INBOX'), (user_idnr,'Gesendete=20
Objekte'), (user_idnr,'Entw=C3=BCrfe'), (user_idnr,'Junk'),=20
(user_idnr,'Papierkorb'); END; $$ LANGUAGE plpgSQL;

But I don't know how to do the trigger. Can someone help me?

mfg zmi
=2D-=20
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0660 / 415 65 31                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net                  Key-ID: 1C1209B4


--nextPart2321333.nZD1C8iBUU
Content-Type: application/pgp-signature; name=signature.asc 
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (GNU/Linux)

iEYEABECAAYFAkmfvrcACgkQzhSR9xwSCbRABQCghV2ocyZOpszcM5bsfbpz7Z98
olYAn1MkJfIWxwpvDmqW13vIa6FP4KtG
=4Bin
-----END PGP SIGNATURE-----

--nextPart2321333.nZD1C8iBUU--