Thread: drop table in pgsql

Hi,

  How do I drop a table under plpgsql ?
  When  I  invoke a function which contains eg: DROP TABLE TMP a get a
  backend crash no matter if this table exists or not.

  I  can always do it the hard way and put DROP ... in an SQL function
  and then invoke DROP indirectly but do I have to ?

Please help me
Pawel Pierscionek

>
> Hi,
>
>   How do I drop a table under plpgsql ?
>   When  I  invoke a function which contains eg: DROP TABLE TMP a get a
>   backend crash no matter if this table exists or not.
>
>   I  can always do it the hard way and put DROP ... in an SQL function
>   and then invoke DROP indirectly but do I have to ?
>
> Please help me
> Pawel Pierscionek

    Huh  -  intersting.  Not that I think it's good to change the
    database  schema  from  inside  of  functions,  but  if   SQL
    functions can do it, PL/pgSQL should too. Will take a look at
    it.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

On Wed, 6 Jan 1999, Jan Wieck wrote:

> >
> > Hi,
> >
> >   How do I drop a table under plpgsql ?
> >   When  I  invoke a function which contains eg: DROP TABLE TMP a get a
> >   backend crash no matter if this table exists or not.
> >
> >   I  can always do it the hard way and put DROP ... in an SQL function
> >   and then invoke DROP indirectly but do I have to ?
> >
> > Please help me
> > Pawel Pierscionek
>
>     Huh  -  intersting.  Not that I think it's good to change the
>     database  schema  from  inside  of  functions,  but  if   SQL
>     functions can do it, PL/pgSQL should too. Will take a look at
>     it.
>
I don't like changing db shema that way too, but there is no support for
temporary tables and I have to use drop before I go with select sth into
temp_db.

Pawel Pierscionek

Hi,

    there  was  recently discussion about schema changes (utility
    queries) inside of PL functions etc.

    I've thought about it some more and came  to  the  conclusion
    that  it  doesn't  make  much sense to support something like
    that in PL/pgSQL.

    The reason for this is that PL/pgSQL does  prepare  and  save
    ALL  plans on the first call of the function. As it is now, a
    PL/pgSQL function used once in a connection will  get  broken
    if  an  object  it uses gets dropped and recreated. The saved
    SPI plans still use the OLD object, which isn't available any
    more.  So  the  function needs to be recompiled and that will
    only happen on a new backend connection.

    There is no syntax that could tell PL/pgSQL  not  to  save  a
    particular  prepared  plan (like in PL/Tcl where planning and
    saving is done separately,  so  the  programmer  has  control
    which plans to save and which not).

    Since  dropping  an  object  in Postgres is a general problem
    that affects saved SPI plans (not  only  in  PL,  C  language
    functions  using  the SPI saved plan feature will break too),
    I'll not add support for it to PL/pgSQL. I think this problem
    will  also  show up if any function using prepared plans will
    be used on the temp tables Bruce currently is working on.




--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #