Postgres Pro
Downloads
Home   >   mailing lists

Thread: DDL in EDB-SPL

DDL in EDB-SPL

From
Heikki Linnakangas
Date:
While looking at the package function precedence problem, I bumped into
another unrelated bug:

According to a quick Google search, Oracle doesn't accept DDL in PL/SQL;
you have to use EXECUTE IMMEDIATE to do that. Trying to run DDL in the
edb-spl fails with a bizarre error message. For example, for CREATE
TABLE footable (full test case attached):
ERROR:  syntax error at or near "footable"
LINE 1: CREATE footable2 (id integer)

So the TABLE token seems to be stripped away somewhere. This begs the
question of what happens with CREATE TEMPORARY TABLE. Lo and behold, it
does what you might've guessed, kind of. TEMPORARY is stripped away,
leaving just "CREATE TABLE <tablename>". However, we've set the package
namespace as the special namespace, and that's the current "default
creation namespace". Therefore the table gets created inside the package
namespace.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com
DROP PACKAGE foopack;

CREATE PACKAGE foopack
IS
  foovar integer;
  PROCEDURE fooproc;
END;

CREATE PACKAGE BODY foopack
IS

  PROCEDURE fooproc IS
  BEGIN
    CREATE TEMPORARY TABLE footable2 (id integer);
  END;
END;

exec foopack.fooproc;

Re: DDL in EDB-SPL

From
"Pavel Stehule"
Date:
Wrong address :)

Pavel

On 12/12/2007, Heikki Linnakangas <heikki@enterprisedb.com> wrote:
> While looking at the package function precedence problem, I bumped into
> another unrelated bug:
>
> According to a quick Google search, Oracle doesn't accept DDL in PL/SQL;
> you have to use EXECUTE IMMEDIATE to do that. Trying to run DDL in the
> edb-spl fails with a bizarre error message. For example, for CREATE
> TABLE footable (full test case attached):
> ERROR:  syntax error at or near "footable"
> LINE 1: CREATE footable2 (id integer)
>
> So the TABLE token seems to be stripped away somewhere. This begs the
> question of what happens with CREATE TEMPORARY TABLE. Lo and behold, it
> does what you might've guessed, kind of. TEMPORARY is stripped away,
> leaving just "CREATE TABLE <tablename>". However, we've set the package
> namespace as the special namespace, and that's the current "default
> creation namespace". Therefore the table gets created inside the package
> namespace.
>
> --
>    Heikki Linnakangas
>    EnterpriseDB   http://www.enterprisedb.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>
>

Re: DDL in EDB-SPL

From
Heikki Linnakangas
Date:
Pavel Stehule wrote:
> Wrong address :)

Shit! I knew this was going to happen eventually, because when I start
to type "pa..." in the address bar, patches@postgresql.org is the first
hit...

Sorry for the noise, please ignore...

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: DDL in EDB-SPL

From
"Joshua D. Drake"
Date:
Pavel Stehule wrote:
> Wrong address :)

O.k. now that makes more sense :)

Joshua D. Drake

>
> Pavel
>
> On 12/12/2007, Heikki Linnakangas <heikki@enterprisedb.com> wrote:
>> While looking at the package function precedence problem, I bumped into
>> another unrelated bug: