[SQL] Trigger
Could someone send me a quick example of a trigger.
Re: [SQL] Trigger
Craig May wrote: > > Could someone send me a quick example of a trigger. Hope this helps. Chris Ryan <<< Clip below and execute to create example >>> -- -- FUNCTION: trigger_last_updated -- -- DESCRIPTION: -- This is a function called by the table triggers to update the last_updated -- field on insert and updates. -- create function trigger_last_updated() returns opaque as 'begin new.last_updated := now(); return new; end;' language 'plpgsql'; -- -- TABLE: test_tbl -- -- DESCRIPTION: -- A simple table to test my trigger -- create table test_tbl ( some_field varchar(10), last_updated timestamp not null default now() ); -- -- TRIGGER: trigger_insert_update_test_tbl -- -- DESCRIPTION: -- This is the trigger called on insert and updates of all the table that -- has the last_updated field. It will use the function trigger_last_updated -- The cool thing here is the function doesn't make specific reference to the -- table so you could create a different trigger for each table with the field -- last_updated and use the same function. -- create trigger trigger_insert_update_test_tbl before insert or update on test_tbl for each row execute procedure trigger_last_updated();
Re: [SQL] Trigger
Chris Ryan wrote: > > Craig May wrote: > > > > Could someone send me a quick example of a trigger. > > Hope this helps. > > Chris Ryan > -- snipped code -- I am so sorry but you may have noticed my email client wrapped lines it shouldn't have. I have attached the file this time. Chris Ryan -- -- FILE: trigger_example.sql -- -- DESCRIPTION: -- This file shows the basics of creating a table with a trigger -- -- Chris Ryan <[EMAIL PROTECTED]> 09/06/2000 -- -- GENERAL DISCLAIMER: -- Please feel free to use this in any way you see fit to copy, modify, -- redistribute, etc.. I provide not warranty of the code nor may I be held -- responsible for it's use/misuse should something bad happen including -- intentional or acts of god. -- -- -- FUNCTION: trigger_last_updated -- -- DESCRIPTION: -- This is a function called by the table triggers to update the last_updated -- field on insert and updates. -- create function trigger_last_updated() returns opaque as 'begin new.last_updated := now(); return new; end;' language 'plpgsql'; -- -- TABLE: test_tbl -- -- DESCRIPTION: -- A simple table to test my trigger -- create table test_tbl ( some_field varchar(10), last_updated timestamp not null default now() ); -- -- TRIGGER: trigger_insert_update_test_tbl -- -- DESCRIPTION: -- This is the trigger called on insert and updates of all the table that -- has the last_updated field. It will use the function trigger_last_updated -- The cool thing here is the function doesn't make specific reference to the -- table so you could create a different trigger for each table with the field -- last_updated and use the same function. -- create trigger trigger_insert_update_test_tbl before insert or update on test_tbl for each row execute procedure trigger_last_updated();
Re: [SQL] Protection of tables by group and not by users
Hi there, I don't quite know what really want to do, however, postgres support group , so you can create a group one time, then grant or revoke this group of people from an object just need the group name. see CREATE GROUP groupname [EMAIL PROTECTED] wrote: > > Hello, > > Is it possible to protect a table of Postgresql by a group of persons instead of >giving a list > of persons ? > > Thanks for your answers > > Regards > nicolas michaud -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Cascading Deletes
I think a references constraint on ID referencing _ID with ON DELETE CASCADE should do what you want. Stephan Szabo [EMAIL PROTECTED] On Wed, 6 Sep 2000, Craig May wrote: > Hi, > > I have a tables having this structure: > > ID (int) | _ID (int) | Name (String) > > > _ID is the parent of ID. > > I'm looking for the best method to perform a cascade delete. For example, I > delete _ID, it in turn deletes ID, and should ID also be an entry within _ID, it > would continue through the chain. > > For example: > > 0 0 Base > 1 0 Sib1 > 2 0 Sib2 > 3 0 Sib3 > 4 1 Sib1_1 > 5 1 Sib1_2 > > > Deleting Base would remove all the entries. Deleting Sib1 would delete Sib1_1 > and Sib1_2. > Can anyone help out here? > > Regards, > Craig May > > Enth Dimension > http://www.enthdimension.com.au >
Re: [SQL] Cascading Deletes
Hi, there, 2 ways may resolve you problem, I believe: 1. re_create your table set _ID as primary key and ID as foreign key with option ON DELETE CASCADE if _ID is unique key. 2. create a rule bind this table, for each row deleting(or create a trigger). Craig May wrote: > Hi, > > I have a tables having this structure: > > ID (int) | _ID (int) | Name (String) > > _ID is the parent of ID. > > I'm looking for the best method to perform a cascade delete. For example, I > delete _ID, it in turn deletes ID, and should ID also be an entry within _ID, it > would continue through the chain. > > For example: > > 0 0 Base > 1 0 Sib1 > 2 0 Sib2 > 3 0 Sib3 > 4 1 Sib1_1 > 5 1 Sib1_2 > > Deleting Base would remove all the entries. Deleting Sib1 would delete Sib1_1 > and Sib1_2. > Can anyone help out here? > > Regards, > Craig May > > Enth Dimension > http://www.enthdimension.com.au -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
[SQL] Re: Auto increment
> 1: Haven't been able to find the officiel manual to pgsql. What's the > complete URL? > 2: How can I make an auto increment with PostGreSQL? is it "inherit"? > 3: The "SHOW TABLE FROM pcfocus" statement, does it work in pgsql? never mind, i found out myself: 1: not officiel, but anyway: http://hea-www.harvard.edu/MST/simul/software/docs/pkg/pgsql/7.0.1/man/m an.html 2: UNIQUE 3: Yes! -- Med venlig hilsen/ Wishes Mads Jensen Homepage: http://www.pcfocus.f2s.com E-Mail: [EMAIL PROTECTED] --
[SQL] crypt function
Hi, my name is Andrew and I'm new to PostgreSQL.
Nowadays I'm using PostgreSQL version 7.02, and I have a
table like this:
name varchar(8)
password varchar(16)
How can I put the password into the table in a encrypted
form, i try to do it:
INSERT INTO TABLE users VALUES
('test',crypt('passtest'));
But postgre returns an error telling that function crypt
didn't exist!!
How can I fix this problem??
Thank you by your attention!!!
Re: [SQL] Re: [ADMIN] 7.0.2 questions on encoding and compilation
Max Pyziur wrote: > On Thu, 31 Aug 2000, Igor N. Avtaev wrote: > > > [EMAIL PROTECTED] wrote: > > > > > Currently, I'm using the the 7.0.2 rpms from the postgresql.org > > > on a RH6.2 install. > > > > > > I have a few questions on it and the use of the -E flag. > > > > > > 1 - can 7.0.2 be optimized for i686 architecture or is > > > it only possible to compile for i386 architecture? > > > > Yes. Changed C compilation flags in template file for your operating > > system. > > Could you please specify which flags are necessary? For my OS - SCO UnixWare 2.1.3 (with SCO SDK): -K pentium or -K p6 > Thanks! > > > > > > > > Max Pyziur BRAMA - Gateway Ukraine > > > [EMAIL PROTECTED] http://www.brama.com/ > [recycle] > Best regards, Igor
[SQL] Re: Cascading Deletes
> 1. re_create your table set _ID as primary key This is not going to work unless a parent is prohibited from having more than one child. From the brief sample data supplied, this is clearly not the case. Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com
Re: [SQL] crypt function
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> INSERT INTO TABLE users VALUES ('test',crypt('passtest'));
AFAIK there is no builtin crypt function.
If you want one, use PL/perl to call the perl crypt function.
Ian Turner
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.1 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQE5tytdfn9ub9ZE1xoRAtxUAJ41TmWz/sloQbVXoUeVniGs5TW71gCfTctK
Ow32lDg7IL9Fqr8SFCzT5gs=
=YCUp
-END PGP SIGNATURE-
Re: [SQL] Re: [ADMIN] 7.0.2 questions on encoding and compilation
RTFM for gcc -mcpu=i686 On Wed, 6 Sep 2000, Max Pyziur wrote: > > > On Thu, 31 Aug 2000, Igor N. Avtaev wrote: > > > [EMAIL PROTECTED] wrote: > > > > > Currently, I'm using the the 7.0.2 rpms from the postgresql.org > > > on a RH6.2 install. > > > > > > I have a few questions on it and the use of the -E flag. > > > > > > 1 - can 7.0.2 be optimized for i686 architecture or is > > > it only possible to compile for i386 architecture? > > > > Yes. Changed C compilation flags in template file for your operating > > system. > > Could you please specify which flags are necessary? > > Thanks! > > > > > > > > Max Pyziur BRAMA - Gateway Ukraine > > > [EMAIL PROTECTED] http://www.brama.com/ > [recycle] > >
Re: [SQL] Re: [ADMIN] 7.0.2 questions on encoding and compilation
On Thu, 31 Aug 2000, Igor N. Avtaev wrote: > [EMAIL PROTECTED] wrote: > > > Currently, I'm using the the 7.0.2 rpms from the postgresql.org > > on a RH6.2 install. > > > > I have a few questions on it and the use of the -E flag. > > > > 1 - can 7.0.2 be optimized for i686 architecture or is > > it only possible to compile for i386 architecture? > > Yes. Changed C compilation flags in template file for your operating > system. Could you please specify which flags are necessary? Thanks! > > > > Max Pyziur BRAMA - Gateway Ukraine > > [EMAIL PROTECTED] http://www.brama.com/ [recycle]
[SQL] RE: [ADMIN] 7.0.2 questions on encoding and compilation
On Wed, 6 Sep 2000, vijayendra mohan agrawal wrote: > 2. createdb -E option can be used, but it seems it needs a re-compilation > which will make "pg_encoding" in your pgsql/bin directory. configure > with --enable-multibyte option and re-install postgreSQL... it should serve > the purpose. What if you just want to use single byte encodings such as Windows-CP1251? Which configuration option should you use? > 3. execute "createdb --help" for more details. --encoding=ENCODING should be > given for creating db with encoding, here "ENCODING" is the type of encoding > you want... > > Hope this helps... > > Thanks and regards, Thanks! and I have read the README.mb file. > Vijay Max Pyziur > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of [EMAIL PROTECTED] > Sent: Thursday, August 31, 2000 2:33 AM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: [ADMIN] 7.0.2 questions on encoding and compilation > > > Currently, I'm using the the 7.0.2 rpms from the postgresql.org > on a RH6.2 install. > > I have a few questions on it and the use of the -E flag. > > 1 - can 7.0.2 be optimized for i686 architecture or is > it only possible to compile for i386 architecture? > > 2 - Can createdb -E someencoding be used "straight out of the box" > with Postgresql 7.0.2 or does support need to be compiled into > the function? > > 3 - What are the list of arguments which can be taken with the > command createdb -E ? > > > > Thanks! > > > > > Max Pyziur BRAMA - Gateway Ukraine > [EMAIL PROTECTED] http://www.brama.com/ > >
