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

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

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 (view raw or flat)
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.

In response to

Responses

pgsql-general by date

Next:From: leaf_yxjDate: 2012-04-03 17:54:35
Subject: Re: Please help me to take a look of the erros in my functions. Thanks.
Previous:From: Thomas KellererDate: 2012-04-03 17:48:57
Subject: Re: views, queries, and locks

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