From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | leaf_yxj <leaf_yxj(at)163(dot)com> |
Cc: | 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-02 20:13:04 |
Message-ID: | CAFj8pRCRO-5y5DBgezteuJVg7usrqd_diSu-gd9fRr5SQRmFuA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)163(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
From | Date | Subject | |
---|---|---|---|
Next Message | Bartosz Dmytrak | 2012-04-02 20:28:44 | Re: Please help me to take a look of the erros in my functions. Thanks. |
Previous Message | leaf_yxj | 2012-04-02 20:02:02 | Please help me to take a look of the erros in my functions. Thanks. |