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

Hi Bartek
One more question, In oracle, when you create table using the default option, the parent table can't be delete if there is any child table exist. Usually, I won't use the cascade option.  I will truncate or delete one by one. what is postgresql default for these???

 

 

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.