Re: [GENERAL] [8.1] drop table in plpgsql function

[John DeSoi]

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


---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] [8.1] drop table in plpgsql function

[Sergey Karin]

-- Forwarded message --
From: Sergey Karin [EMAIL PROTECTED]
Date: 11.11.2005 13:59
Subject: [8.1] drop table in plpgsql function
To: pgsql-general@postgresql.org


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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] [8.1] drop table in plpgsql function

[Sergey Karin]

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

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly