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

I tried to create function to truncate table
1) when the user call the function just specify the tablename
2) the user can use the function owner privilege to execute the function.

But I got the errors as follows. Please help me to take a look.

Thanks.

Regards.

Grace
------ function :

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) || '
CASCADE;';
 ELSE
     The tablename doesn't exist.doesn
     END IF ;
 END;
 $$ LANGUAGE 'plpgsql' security definer;

---- errors.
ERROR:  syntax error at or near "$2"
LINE 1: SELECT   $1  IN  $2
                         ^
QUERY:  SELECT   $1  IN  $2
CONTEXT:  SQL statement in PL/PgSQL function "truncate_t" near line 6

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

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>:
> I tried to create function to truncate table
> 1) when the user call the function just specify the tablename
> 2) the user can use the function owner privilege to execute the function.
>
> But I got the errors as follows. Please help me to take a look.
>
> Thanks.
>
> Regards.
>
> Grace
> ------ function :
>
> 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) || '
> CASCADE;';
>  ELSE
>     The tablename doesn't exist.doesn
>     END IF ;
>  END;
>  $$ LANGUAGE 'plpgsql' security definer;
>
> ---- errors.
> ERROR:  syntax error at or near "$2"
> LINE 1: SELECT   $1  IN  $2
>                         ^
> QUERY:  SELECT   $1  IN  $2
> CONTEXT:  SQL statement in PL/PgSQL function "truncate_t" near line 6
>
> --
> 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-tp5613507p5613507.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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
multiplerows 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.

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

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.

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.

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.

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.

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

On 04/03/12 11:13 AM, leaf_yxj wrote:
> Hi John, Thanks for your reply. Just to confirm :  so truncate table
> means the space will be reclaim for reuse ???

yes, all the tablespace is immediately returned to the file system when
the transaction with the TRUNCATE statement commits.



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