Skip site navigation (1) Skip section navigation (2)

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

From: Bartosz Dmytrak <bdmytrak(at)gmail(dot)com>
To: leaf_yxj <leaf_yxj(at)163(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Re: Please help me to take a look of the erros in my functions. Thanks.
Date: 2012-04-03 19:11:50
Message-ID: CAD8_UcbT3VY9ULwNoEcV27gdHfoKc7QKjjTkt4hdEta6TuG6=g@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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(at)163(dot)com>

> 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]<http://user/SendEmail.jtp?type=node&node=5615961&i=0>>
> 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]<http://webmail.mail.163.com/user/SendEmail.jtp?type=node&node=5615860&i=0>
> >
>
>> 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]<http://webmail.mail.163.com/user/SendEmail.jtp?type=node&node=5615860&i=1>
>> )
>>
>> 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<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>
>
>
>
> ------------------------------
> View this message in context: Re:Re: Please help me to take a look of the
> erros in my functions. Thanks.<http://postgresql.1045698.n5.nabble.com/Please-help-me-to-take-a-look-of-the-erros-in-my-functions-Thanks-tp5613507p5615961.html>
>  Sent from the PostgreSQL - general mailing list archive<http://postgresql.1045698.n5.nabble.com/PostgreSQL-general-f1843780.html>at Nabble.com.
>

In response to

pgsql-general by date

Next:From: Merlin MoncureDate: 2012-04-03 19:13:58
Subject: Re: views, queries, and locks
Previous:From: Jon NelsonDate: 2012-04-03 18:41:20
Subject: Re: views, queries, and locks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group