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
