Re: Error creating function

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Rebecca Clarke <rebecca(at)clarke(dot)net(dot)nz> 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
>