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@xxxxxxxxx>
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@xxxxxxx>:
> 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@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
