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

[Bartosz Dmytrak <bdmytrak@xxxxxxxxx>]

If You mean parent and child tables as connected by relation (primery key - foreign key) then child table will be truncated regardless the relation type, if CASCADE exists.

This applies to PG 9.1.3 (I've got only this version).

Regards,
Bartek

2012/4/3 leaf_yxj <leaf_yxj@xxxxxxx>

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.