From: | Sergey Karin <sergey(dot)karin(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | [8.1] "drop table" in plpgsql function |
Date: | 2005-11-11 10:59:51 |
Message-ID: | b78883bf0511110259i7f5dff67g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
hi all!
I have found a strange error.
panorama2=# select version();
version
------------------------------------------------------------------------
PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
(1 row)
I have next function in file ./f_safe_drop_table.sql:
------------------------------
create or replace function f_safe_drop_table(varchar) returns bool as'
declare
table_name alias for $1;
tcount int4;
begin
select f_is_table_exist(table_name) into tcount;
if(tcount > 0) then
drop table table_name;
return true;
end if;
return false;
end
'language 'plpgsql';
======================
when I create my function in psql, I have next error:
-----------------------
panorama2=# \i ./f_safe_drop_table.sql
psql:./f_safe_drop_table.sql:29: ERROR: syntax error at or near "$1"
at character 13
QUERY: drop table $1
CONTEXT: SQL statement in PL/PgSQL function "f_safe_drop_table" near line 10
psql:./Functions/Misc/f_safe_drop_table.sql:29: LINE 1: drop table $1
psql:./Functions/Misc/f_safe_drop_table.sql:29: ^
-----------------------
But if I replace "drop table table_name" on "dyn_query := 'drop table
' || table_name; execute dyn_query" all works fine!
------------------------------
create or replace function f_safe_drop_table(varchar) returns bool as'
declare
table_name alias for $1;
tcount int4;
dyn_query varchar;
begin
select f_is_table_exist(table_name) into tcount;
if(tcount > 0) then
dyn_query := 'drop table ' || table_name;
execute dyn_query;
return true;
end if;
return false;
end
'language 'plpgsql';
======================
panorama2=# \i ./f_safe_drop_table.sql
CREATE FUNCTION
Also both functions on PG8.0 works fine!
Sergey Karin
From | Date | Subject | |
---|---|---|---|
Next Message | Andrus | 2005-11-11 12:12:07 | Re: Best way to use indexes for partial match at |
Previous Message | Martijn van Oosterhout | 2005-11-11 10:52:38 | Re: Best way to use indexes for partial match at |