Re: 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>:

> 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

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