Re: Error creating function

[Rebecca Clarke <rebecca@xxxxxxxxxxxxx>]

Hi Tom

Thanks for your reply. I was restoring one schema (with data) at a time from 8.3 db to a pre-created empty 8.4 db. I received a lot of errors which game down to me not restoring the public schema first. I also realized, I had not created plpgsql language for the 8.4 db before I  did the restoration. So after I created the language and restarted the restoration from scratch with the public schema first, I no longer received this error.

Thanks

Rebecca

On Wed, Jul 20, 2011 at 3:46 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Rebecca Clarke <rebecca@xxxxxxxxxxxxx> writes:
> I'm doing a restore into Postgresql 8.4, postgis 1.5.1 and I'm getting the
> following error when it creates the below function:

> pg_restore: creating FUNCTION _get_buffer(geometry, double precision,
> integer)
> pg_restore: [archiver (db)] Error from TOC entry 966; 1255 49162661 FUNCTION
> _get_buffer(geometry, double precision, integer) postgres
> pg_restore: [archiver (db)] could not execute query: ERROR:  SQL function
> cannot accept shell type geometry
>     Command was: CREATE FUNCTION _get_buffer(_geom geometry, _radius double
> precision, _density integer, OUT the_geom geometry) RETURNS geome...

> I don't understand what 'cannot accept shell type geometry' means? Can
> anyone help my lack of knowledge?

Hmm, that is interesting.  A "shell" type is a SQL base data type that
has been declared to the database but its properties are not yet filled
in.  This is needed because the properties include I/O functions, which
have to be declared to take or return the data type, so there's a
circularity involved.  The standard solution is

       CREATE TYPE typename;   -- this creates typename as a shell

       CREATE FUNCTION typename_in(cstring) RETURNS typename ...

       CREATE FUNCTION typename_out(typename) RETURNS cstring ...

       CREATE TYPE typename (input = typename_in, output = typename_out, ...);

The last step changes the type from a shell into a real, usable
datatype.

So what you've apparently got is a situation where that last step got
missed for the geometry type, or else the _get_buffer function somehow
got inserted into the middle of this sequence.  I've not heard of that
happening to people before, so I wonder if you could provide the exact
step-by-step of what you did.

                       regards, tom lane