From: | leaf_yxj <leaf_yxj(at)163(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Please help me to take a look of the erros in my functions. Thanks. |
Date: | 2012-04-03 17:49:23 |
Message-ID: | 61a51b64.b589.1367952e8ee.Coremail.leaf_yxj@163.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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+s1045698n5615860h2(at)n5(dot)nabble(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | leaf_yxj | 2012-04-03 17:54:35 | Re: Please help me to take a look of the erros in my functions. Thanks. |
Previous Message | Thomas Kellerer | 2012-04-03 17:48:57 | Re: views, queries, and locks |