[SQL] Create trigger for auto update function
Hi to all, I have a table: create table hoy( id serial, pass varchar(40), pass_md5 varchar(40); Now, I want to write a trigger function that automatically updates the pass_md5 with the md5 function of the pass. I tried this: CREATE FUNCTION update_pass(integer) RETURNS integer AS $$ UPDATE hoy SET pass_md5=md5(pass) WHERE id=$1; SELECT 1;$$ LANGUAGE SQL; and CREATE TRIGGER triger_users_pass_md5 AFTER INSERT OR UPDATE ON hoy EXECUTE PROCEDURE update_pass(integer); But it works not. When I create the trigger it says that function does not exist. I also tried with: CREATE OR REPLACE FUNCTION user2(integer)RETURNS TRIGGER AS'BEGIN UPDATE users SET pass_md5=md5(pass) WHERE id=$1; return NULL;END'language plpgsql; the same Need some help Andy.
Re: [SQL] Create trigger for auto update function
CREATE TRIGGER triger_users_pass_md5 AFTER INSERT OR UPDATE ON hoy EXECUTE PROCEDURE update_pass(integer); Try : FOR EACH ROW EXECUTE ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Create trigger for auto update function
Hello Andrei, Monday, July 18, 2005, 2:24:41 PM, you wrote: AB> Hi to all, AB> I have a table: AB> create table hoy( AB> id serial, AB> pass varchar(40), AB> pass_md5 varchar(40); AB> Now, I want to write a trigger function that automatically updates the pass_md5 with the md5 function of the pass. AB> I tried this: AB> CREATE FUNCTION update_pass(integer) RETURNS integer AS $$ AB> UPDATE hoy SET pass_md5=md5(pass) WHERE id=$1; AB>SELECT 1; AB> $$ LANGUAGE SQL; AB> and AB> CREATE TRIGGER triger_users_pass_md5 AB> AFTER INSERT OR UPDATE AB> ON hoy AB> EXECUTE PROCEDURE update_pass(integer); What will be the param of the trigger procedure? Try this way: CREATE FUNCTION update_pass() RETURNS integer AS $$ UPDATE hoy SET pass_md5=md5(pass) WHERE id=new.id; SELECT 1; $$ LANGUAGE SQL; CREATE TRIGGER triger_users_pass_md5 AFTER INSERT OR UPDATE ON hoy FOR EACH ROW EXECUTE PROCEDURE update_pass; DAQ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] About retrieving objects' priviledge info such as grantee & its privileges for a specific object (view, table, function, etc.)
Hello, May I know the commands to retrieve objects' privileges info please? Something like: Object Type, Object name,creator,grantee, privilege, is_creatable = table T1 user1user2 select, update, delete no table T1 user1user3 selectno view V1user2 user4 selectno P.S. (postgreSQL 8.0.1) Thanks a lot, Emi ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Dumping table definitions
I am looking for a way to reformat the information that is generated from \d mytable into SQL syntax, such that the table can be recreated with 'psql -f mytable.sql' complete with index and constraint definitions. I can do awk and sed commands to do this if I need to, but first wanted to check if Pg already had tools to export the table structure (without the data). Does it? Also, Is there a way to export the structure of all tables at once instead of one table at a time? Mark begin:vcard fn:Mark Fenbers n:Fenbers;Mark org:DoC/NOAA/NWS/OHRFC adr:;;1901 South SR 134;Wilmington;OH;45177-9708;USA email;internet:[EMAIL PROTECTED] title:Sr. HAS Meteorologist tel;work:937-383-0430 x246 x-mozilla-html:TRUE url:http://weather.gov/ohrfc version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Dumping table definitions
use pg_dump with the --schema-only and --table= params -- Original Message --- From: "Mark Fenbers" <[EMAIL PROTECTED]> To: Pg SQL Discussion Group Sent: Mon, 18 Jul 2005 12:50:54 -0400 Subject: [SQL] Dumping table definitions > I am looking for a way to reformat the information that is generated from > \d mytable > into SQL syntax, such that the table can be recreated with 'psql -f > mytable.sql' complete with index and constraint definitions. I can do > awk and sed commands to do this if I need to, but first wanted to check > if Pg already had tools to export the table structure (without the > data). Does it? > > Also, Is there a way to export the structure of all tables at once > instead of one table at a time? > > Mark --- End of Original Message --- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Dumping table definitions
"Mark Fenbers" <[EMAIL PROTECTED]> writes: > I am looking for a way to reformat the information that is generated from > \d mytable > into SQL syntax, such that the table can be recreated with 'psql -f > mytable.sql' complete with index and constraint definitions. I can do > awk and sed commands to do this if I need to, but first wanted to check > if Pg already had tools to export the table structure (without the > data). Does it? pg_dump with the -s switch is a much better way ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Dumping table definitions
On 7/18/05, Mark Fenbers <[EMAIL PROTECTED]> wrote: > I am looking for a way to reformat the information that is generated from > \d mytable > into SQL syntax, such that the table can be recreated with 'psql -f > mytable.sql' complete with index and constraint definitions. I can do > awk and sed commands to do this if I need to, but first wanted to check > if Pg already had tools to export the table structure (without the > data). Does it? pg_dump is your friend! Use option -s (--schema-only). It will dump the information you want. :-) Regards, Dawid ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
