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.steh...@gmail.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_...@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 >
