Re: [8.1] "drop table" in plpgsql function - Mailing list pgsql-general

Sergey,

On Nov 14, 2005, at 2:03 AM, Sergey Karin wrote:

> I have found a strange error.

Perhaps your f_is_table_exist function is not working correctly.

Using variables for table references is not supported in plpgsql (any
version). You must use execute. Here is my test on 8.0.4:

create table test_table(a text, b integer);

create or replace function del_table(varchar) returns boolean as '
declare
     tname alias for $1;
begin
     drop table tname;
     return true;
end;
' language plpgsql;

select del_table('test_table');


CREATE TABLE
CREATE FUNCTION
psql:16: ERROR:  syntax error at or near "$1" at character 13
QUERY:  drop table  $1
CONTEXT:  PL/pgSQL function "del_table" line 4 at SQL statement
psql:16: LINE 1: drop table  $1
psql:16:


It works Ok if you change the drop line to

execute ''drop table '' || tname;

Also, the example that you indicate is working with execute is not
quoted correctly.

Best,



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL