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

Hello

" IF stmt IN statements then " is nonsense.

use trapping exceptions instead

BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(_tablename) || ' CASCADE';
EXCEPTION WHEN undefined_table THEN
RAISE EXCEPTION 'your own exception, when you like';
END;

Regards

Pavel

2012/4/2 leaf_yxj <leaf_yxj@163.com>:

That is right, there is no sense to use cursors here...

CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;';
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table "%" does not exists', tablename;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

this works fine for me.
Regards,
Bartek

2012/4/2 Pavel Stehule <pavel.stehule@gmail.com>

On 2 Apr 2012, at 22:28, Bartosz Dmytrak wrote:

That is right, there is no sense to use cursors here...

I think you're wrong there: The OP is querying a system table for tables of a certain name, which I expect can contain multiple rows for tables of the same name in different schema's.

Of course, that may not be applicable to the her situation.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

On 2 Apr 2012, at 22:02, leaf_yxj wrote:

CREATE OR REPLACE FUNCTION truncate_t(tablename IN VARCHAR) RETURNS void AS
$$
DECLARE
stmt RECORD;
statements CURSOR FOR SELECT tablename FROM pg_catalog.pg_tables;
BEGIN
IF stmt IN statements then
EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || '

I think you meant to use a FOR LOOP there, not IF. IF does not know to fetch a record from a CURSOR (hmm... should it perhaps?).

http://www.postgresql.org/docs/9.1/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-FOR-LOOP

CASCADE;';
ELSE
The tablename doesn't exist.doesn
END IF ;
END;
$$ LANGUAGE 'plpgsql' security definer;

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

2012/4/3 Alban Hertroys <haramrae@gmail.com>

On 2 Apr 2012, at 22:28, Bartosz Dmytrak wrote:

That is right, there is no sense to use cursors here...

I think you're wrong there: The OP is querying a system table for tables
of a certain name, which I expect can contain multiple rows for tables of
the same name in different schema's.

Of course, that may not be applicable to the her situation.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

hmm...

if tablename variable contains schema name this function will never work,
because:
quote_ident ('aa.aaa') gives "aa.aaa" what is not proper fully qualified
name, should be "aa"."aaa".
So, my assumption is tablename variable contains only table name. If this
is only table name, without schema name then postgre will try to truncate
table only in schema where this table could be found (according to
search_path parameter). It is not possible to have more then one table with
the same name in the same schema.

Grace wrote:
*"I tried to create function to truncate table"*
this drives me to think about one table not all of them in database, but
cursor statement could be misleading.

I think it is not a good idea to truncate all tables with the same name in
all schemas (maybe this is Grace's intention - don't know).

BTW, *tablename *column of *pg_catalog.pg_tables* view contains only table
name without schema, so this statement will NOT truncate all tables with
the same name accross all schemas because of search_path.
http://www.postgresql.org/docs/9.1/static/view-pg-tables.html

Regards,
Bartek

***********************************************************

CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;';
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table "%" does not exists', tablename;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

***********************************************************

This works . Thank you very much.

***********************************************************

CREATE OR REPLACE FUNCTION truncate_t (tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;';
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table "%" does not exists', tablename;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

***********************************************************
This works,too. Thank you very much. What's the difference between ( IN
tablename text)
and ( tablename text).

---------- one more questions thanks.

After I created the function sucessfully , I want to execute the function. I
get errors as follows :

rrp=> select truncate_t(t1);
ERROR: column "t1" does not exist
LINE 1 : select truncate_t(t1);
^

rrp=> select truncate_t(rrp.t1);
ERROR:missing FROM-clause entry for table "rrp"
LINE 1 : select truncate_t(rrp.t1);
^

--
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-tp5613507p5615212.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Bartek, Thanks. The reason I use the cursor is that I want to check the table
is in the pg_tables or not,
If it exists, the function will execute successfully, if not, it will raise
the message that the table doesn't exist. For the schema part, I assume the
people has set the search_path to that schema which the table is in and
because our database only has one schema. So I assume they have the right
search_path.

Thanks for your advice. It helps me a lot.

--
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-tp5613507p5615238.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Alban, Thanks.

--
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-tp5613507p5615244.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 04/03/2012 07:01 AM, leaf_yxj wrote:

***********************************************************

CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;';
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table "%" does not exists', tablename;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

***********************************************************

This works . Thank you very much.

***********************************************************

CREATE OR REPLACE FUNCTION truncate_t (tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;';
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table "%" does not exists', tablename;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER STRICT;

***********************************************************
This works,too. Thank you very much. What's the difference between ( IN
tablename text)
and ( tablename text).

---------- one more questions thanks.

After I created the function sucessfully , I want to execute the function. I
get errors as follows :

rrp=> select truncate_t(t1);
ERROR: column "t1" does not exist
LINE 1 : select truncate_t(t1);
^

You need to pass in a text value:

select truncate_t('t1');

rrp=> select truncate_t(rrp.t1);
ERROR:missing FROM-clause entry for table "rrp"
LINE 1 : select truncate_t(rrp.t1);
^

Same above.

--
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-tp5613507p5615212.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Adrian Klaver
adrian.klaver@gmail.com

Adrian, Thanks. Even I try use '' to quote the character. I still get the
error as follows :

rrp=> truncate table t1;
TRUNCATE TABLE
rrp=> select truncate_t('t1');
ERROR: table "t1" does not exist

Thanks.

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-tp5613507p5615292.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

leaf_yxj <leaf_yxj@163.com> writes:

***********************************************************
CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;';

I think you need a space there:

EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ' CASCADE;';

EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table "%" does not exists', tablename;

It's really a pretty bad idea to print your own message instead of using
the system's message. In this case, you would have figured out the
problem immediately if you'd seen the real error message, which was
presumably bleating about "t1cascade".

regards, tom lane

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.

I think you need a space there:

EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || ' CASCADE;';

indeed, that is my fault - sorry

EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table "%" does not exists', tablename;

It's really a pretty bad idea to print your own message instead of using
the system's message. In this case, you would have figured out the
problem immediately if you'd seen the real error message, which was
presumably bleating about "t1cascade".

Like always, it depends, custom error message has been required by Grace

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Regards,
Bartek

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 <leaf_yxj@163.com>

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@n5.nabble.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.

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]" <ml-node+s1045698n5615860h2@n5.nabble.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-tp5613507p5615961.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 04/03/12 10:49 AM, leaf_yxj wrote:

--- I amn't sure what's differences between truncate and delete in 
postgresql. Could you do me a favour to tell me about this.

delete has to go through and flag each tuple for deletion so vacuum can
eventually go through and reclaim them for reuse. truncate wipes the
whole table out, including 0 length the files.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

Hi John, Thanks for your reply. Just to confirm : so truncate table means the space will be reclaim for reuse ???

Thanks.

Grace

At 2012-04-04 02:01:59,"John R Pierce [via PostgreSQL]" <ml-node+s1045698n5615977h29@n5.nabble.com> wrote:
On 04/03/12 10:49 AM, leaf_yxj wrote:

--- I amn't sure what's differences between truncate and delete in
postgresql. Could you do me a favour to tell me about this.

delete has to go through and flag each tuple for deletion so vacuum can
eventually go through and reclaim them for reuse. truncate wipes the
whole table out, including 0 length the files.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

--
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-tp5613507p5615977.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-tp5613507p5616006.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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@163.com>