Re: Please help me to take a look of the erros in my functions. Thanks.

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(dot)stehule(at)gmail(dot)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_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
>
> --
> 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
>