Re: Please help me to take a look of the erros in my functions. Thanks.

[leaf_yxj <leaf_yxj@xxxxxxx>]

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]" <[hidden email]> 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: Re:Re: Please help me to take a look of the erros in my functions. Thanks.
Sent from the PostgreSQL - general mailing list archive at Nabble.com.