Обсуждение: Bug #710: Fail To Create/Drop Temporary Table IN PL/PGSQL

Поиск
Список
Период
Сортировка

Bug #710: Fail To Create/Drop Temporary Table IN PL/PGSQL

От
pgsql-bugs@postgresql.org
Дата:
CNLIOU (cnliou@eurosport.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Fail To Create/Drop Temporary Table IN PL/PGSQL

Long Description
Hi!

In TODO list, it reads in sub section

"Fix problems with complex temporary table creation/destruction without using PL/PgSQL EXECUTE, needs cache
prevention/invalidation"

in section "SERVER-SIDE LANGUAGES".

According to this statement, I am in the impression that I can create/destruct temporary table with "execute"
statement,but actually I can't.
 

Perhaps this can be added to TODO list, too.

Also, being unable to use temporary table in PL/PGSQL, I can not proceed working on part of my project.

Best Regards,

CN

Sample Code
CREATE FUNCTION test() RETURNS BOOLEAN AS '
DECLARE
  n INTEGER;
BEGIN
  EXECUTE ''CREATE TEMP TABLE temp1 (MyField INTEGER)'';
  INSERT INTO temp1 VALUES(8);

  SELECT MyField INTO n FROM temp1 LIMIT 1;
  RAISE NOTICE ''%'',n;

  EXECUTE ''DROP TABLE temp1'';
  RETURN TRUE;
END;' LANGUAGE 'plpgsql';

database=# select test();
NOTICE:  8
 test
------
 t
(1 row)

database=# select test();
NOTICE:  Error occurred while executing PL/pgSQL function test
NOTICE:  line 5 at SQL statement
ERROR:  Relation 1118219 does not exist

No file was uploaded with this report

Re: Bug #710: Fail To Create/Drop Temporary Table IN PL/PGSQL

От
Stephan Szabo
Дата:
On Thu, 11 Jul 2002 pgsql-bugs@postgresql.org wrote:

> CNLIOU (cnliou@eurosport.com) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description
> Fail To Create/Drop Temporary Table IN PL/PGSQL
>
> Long Description
> Hi!
>
> In TODO list, it reads in sub section
>
> "Fix problems with complex temporary table creation/destruction
> without using PL/PgSQL EXECUTE, needs cache prevention/invalidation"
>
> in section "SERVER-SIDE LANGUAGES".
>
> According to this statement, I am in the impression that I can
> create/destruct temporary table with "execute" statement, but actually
> I can't.
>
> Perhaps this can be added to TODO list, too.
>
> Also, being unable to use temporary table in PL/PGSQL, I can not
> proceed working on part of my project.

You need to use EXECUTE on all statements dealing with the table
you are creating and dropping, but there is an issue that I don't
know of a direct way to select into a field with EXECUTE apart from
what I do below.

CREATE OR REPLACE FUNCTION test() RETURNS BOOLEAN AS '
DECLARE
  n INTEGER;
  r RECORD;
BEGIN
  EXECUTE ''CREATE TEMP TABLE temp1 (MyField INTEGER)'';
  EXECUTE ''INSERT INTO temp1 VALUES(8);'';

  FOR r IN EXECUTE ''SELECT MyField FROM temp1 LIMIT 1'' LOOP
   n := r.MyField;
  END LOOP;
  RAISE NOTICE ''%'',n;

  EXECUTE ''DROP TABLE temp1'';
  RETURN TRUE;
END;' LANGUAGE 'plpgsql';