Обсуждение: PL problem

Hi,

    Another problem with PL (I got no replies to the bug report last Friday
about problems with PL when using FOR UPDATE OF ..).

I think the following may be impossible fomr PL, but I store information
in different tables depending on the type of job that is being handled.
On reset I want to clear these tables, so i store the names of the
tables in a separate table and have a loop in PL

  FOR table IN
      SELECT jb_name FROM jobparams p, jobs j
      WHERE jb_type = type AND j.job = tsk.job
      LOOP
    DELETE FROM table.jb_name
        WHERE job = tsk.job AND id = vid
        AND date >= tsk.fromdate AND date <= tsk.uptodate;
  END LOOP;


and PL does not like a variable in the table name.

ERROR:  parser: parse error at or near "$1"

I guess this may have to do with the way the query plan is stored, so
that this may just be impossible to fix. Could somebody who knows how PL
works please confirm whether I'm asking something impossible here?

Adriaan

At 10:36 +0300 on 14/06/1999, Adriaan Joubert wrote:


> I guess this may have to do with the way the query plan is stored, so
> that this may just be impossible to fix. Could somebody who knows how PL
> works please confirm whether I'm asking something impossible here?

Yep, it is impossible. DELETE FROM takes a table argument, not a string
argument. It may be hard for people to make the distinction, just remember
that:

   DELETE FROM my_table WHERE...

is different from

   DELETE FROM 'my_table' WHERE...

and the second form is illegal.

In C it is easier to do, because the query itself is a string, thus you can
build it from other strings. But in PL/pgsql, the query is not a string. It
is pre-compiled.

Perhaps there is a solution in PL/TCL. In general, in order to do the task
you want to be done, you need to use a language that allows building the
query as a string. Perhaps this should be added as a feature in PL/PgSQL,
as it is added to embedded-sql languages.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma