Bartek, Thanks for your reminding. I don't know why CASCASE doesn't work in my greenplum postgresql database (version 8.2.14). I can create the function successfully without any errors. But when i call it, I alwasy got errors if I include the CASCADE. If I delete the CASCADE, it will works. I don't know why. ---- And I read your link. CASCADE means that the child table will be delete,too. I will do a test again. If possible , Could you help me to do a test of creation and usage of that function? if so, please share me your result. For the truncate and delete , in Oracle , the truncate table can reset the high water mark and the space can be reused. the delete can't reset the high water mark and the space can't be reused. I guess : oracle truncate= truncate + vacuum --- I amn't sure what's differences between truncate and delete in postgresql. Could you do me a favour to tell me about this. Thanks. Regards. Grace
At 2012-04-04 01:15:40,"Bartosz Dmytrak [via PostgreSQL]" <ml-node+s1045698n561586...@n5.nabble.com> wrote: One more thing: TRUNCATE has option CASCASE: http://www.postgresql.org/docs/9.1/static/sql-truncate.html I don't remember since when, but 9.X has this option. Another thing: Do You really need this function..... AFAIK since 8.4 postgres has TRUNCATE privilage on Table http://www.postgresql.org/docs/9.1/static/sql-grant.html this is not the same as DELETE so, I think it is enough to grant this privilage to user Regards, Bartek 2012/4/3 leaf_yxj <[hidden email]> Tom, Thanks. I found out the key issue it. It's because the truncate command can't have the "cascade". For the other people reference. The right funcitons are : *********************************************************** CREATE OR REPLACE FUNCTION truncate_t (IN tablename text) RETURNS VOID AS $$ BEGIN EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';'; EXCEPTION WHEN undefined_table THEN RAISE EXCEPTION 'Table "%" does not exists', tablename; END; $$ LANGUAGE plpgsql SECURITY DEFINER STRICT; *********************************************************** *********************************************************** CREATE OR REPLACE FUNCTION truncate_t (tablename text) RETURNS VOID AS $$ BEGIN EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ';'; EXCEPTION WHEN undefined_table THEN RAISE EXCEPTION 'Table "%" does not exists', tablename; END; $$ LANGUAGE plpgsql SECURITY DEFINER STRICT; *********************************************************** usage : select truncate_t ('aaa'); Thanks everybody's help. Regards. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615529.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list ([hidden email]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general If you reply to this email, your message will be added to the discussion below: http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615860.html To unsubscribe from Please help me to take a look of the erros in my functions. Thanks., click here. NAML -- View this message in context: http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615952.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
