Thread: pgAdmin bug? creating functions

I am having problems creating functions with PgAdmin. I have searched the 
lists at archives.postgresql.org/pgadmin-support, but didn't find anything 
about it.

The basic problem appears to be that PgAdmin may not be setting the 
search_path properly when it issues its updates. The scenario is this:

I am working in a schema which is neither the public schema nor the name of 
the current user. When I try create functions that take arguments that are 
table names, postgres says it can't find the table. For example, if the 
user is xxx and the schema is sss, the following should work
        create or replace function sss.doit(sss.mytable)               ...

If I create this function using psql, it works just fine. However, when I 
look at the function in PgAdmin, I see this
        create or replace function sss.doit(mytable)

(mytable has lost its schema prefix). Mods to the doit function using PgAdmin
result in the following error:
        ERROR: Type "packet_tbl" does not exist.

A similar problem occurs when creating triggers. If doit (above) were a 
trigger function (ie, returns trigger), PgAdmin lists it in the pulldown 
when creating the trigger, but when it tries to create the trigger, the 
following error occurs:
        ERROR: CreateTrigger: function doit() does not exist.

I think it might be a search path issue that the GUI isn't handling properly.

Any help would be appreciated.

---
Michael 

Hi Michael,

> -----Original Message-----
> From: Michael Shapiro [mailto:mshapiro@ncsa.edu]
> Sent: 14 November 2003 19:25
> To: pgadmin-support@postgresql.org
> Subject: [pgadmin-support] pgAdmin bug? creating functions
>
> If I create this function using psql, it works just fine.
> However, when I look at the function in PgAdmin, I see this
>
>          create or replace function sss.doit(mytable)
>

I have just commited a fix to cvs that should resolve this, and allow
you to create functions with class type arguments and return types as
well. This should appear in the next snapshot release (these can be
found at http://snake.pgadmin.org/snapshots for various platforms -
Windows ones are created manually every now and again by Andreas).

> A similar problem occurs when creating triggers. If doit
> (above) were a trigger function (ie, returns trigger),
> PgAdmin lists it in the pulldown when creating the trigger,
> but when it tries to create the trigger, the following error occurs:
>
>          ERROR: CreateTrigger: function doit() does not exist.

This already appears to be fixed.

> I think it might be a search path issue that the GUI isn't
> handling properly.

pgAdmin goes out of it's way to ignore the search path actually :-) It's
safer to prefix everything, some things just get missed though :-(

Thanks for the bug report.

Regards, Dave.

Dave Page wrote:

>Andreas, can you upload a new Win32 snapshot please?
>  
>

Done.

Regards,
Andreas

> -----Original Message-----
> From: Michael Shapiro [mailto:mshapiro@ncsa.uiuc.edu]
> Sent: 18 November 2003 03:31
> To: Dave Page
> Subject: Re: [pgadmin-support] pgAdmin bug? creating functions
>
> I downloaded the snapshot and am using it. It seems to work
> just fine and fixes the problems I was having. Thanks for all
> your help.

You're welcome.

> Is there an option so that the function propery window will
> come up with the same size and placement as it was last left
> in? It is not a big deal, but it would be nice if after I
> open the window and then maximize it, use it, then close it
> and then open it again it comes up maximized. Again, not a
> show stopper for sure -- just that it tends to be more
> window-like if it does that kind of thing.

I thought it did. Is size/position saving not implemented in property
dialogues yet Andreas?

Regards, Dave.

Dave Page wrote:

> 
>
>
>
>I thought it did. Is size/position saving not implemented in property
>dialogues yet Andreas?
>
>  
>
Currently, property dialogs are simply centered.

Regards,
Andreas

> -----Original Message-----
> From: Andreas Pflug [mailto:pgadmin@pse-consulting.de]
> Sent: 18 November 2003 09:56
> To: Dave Page
> Cc: Michael Shapiro; pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] pgAdmin bug? creating functions
>
> Dave Page wrote:
>
> >
> >
> >
> >
> >I thought it did. Is size/position saving not implemented in
> property
> >dialogues yet Andreas?
> >
> >
> >
> Currently, property dialogs are simply centered.

OK, I'll add that as a todo for a rainy day.

Regards, Dave.