Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux