Обсуждение: Creating functions with pgAdmin III

Hi

I don't know if anyone has encountered this problem before.  I
couldn't find any reference to this on the list archives or FAQ.

I can't find the pseudo-type RECORD in the drop-down list of return
types so it is impossible to create functions like "CREATE OR REPLACE
FUNCTION foo (TEXT) RETURNS SET OF RECORD AS..." using the gui.

Currently I have to work around this by manually creating the
function shell from psql or using the query editor and then editing
the function body using pgAdmin.

I've tried pgAdmin 1.0.1 and 1.0.2 against postgreSQL-7.2.3, 7.3.3,
7.3.4 and 7.4 but none of the combinations work.

BTW, this is a _great_ tool which has helped our sysadmins and
junior developers quickly and easily administer, grasp the structure
of our databases and develop their own schemas.

Any ideas?

Ian

Ian Freislich wrote:

>I can't find the pseudo-type RECORD in the drop-down list of return
>types so it is impossible to create functions like "CREATE OR REPLACE
>FUNCTION foo (TEXT) RETURNS SET OF RECORD AS..." using the gui.
>  
>
Not all 'p' types are allowed as return types for functions, but 
apparently the list was too restricted.
I expanded the list (to be precise: allow all for certain exceptions), 
committed to cvs.

>Currently I have to work around this by manually creating the
>function shell from psql or using the query editor and then editing
>the function body using pgAdmin.
>  
>
Yeah, this is what pgAdmin3's sql preview is meant for. Not necessary 
for this special case any more.

>BTW, this is a _great_ tool which has helped our sysadmins and
>junior developers quickly and easily administer, grasp the structure
>of our databases and develop their own schemas.
>  
>

Thanks!

Regards,
Andreas

Michael Shapiro wrote:

>The list in the pulldown is in a random order, especially srt
>to the list of tables in mutilple schemas. It woudld be nice
>if the list was ordered with builtin types listed first, then
>each table within each schema listed next. Within each of the
>builtin, and schemas the list would be alphabetical:
>  
>
Actually it isn't random, but the sort order might not be what you 
expect. It looks like (regard as meta code)
ORDER BY isdomain, isArray, typname


>I would even be great if the various columns could somehow be
>accessible as well (although that would make too large a list if
>put all in one pulldown)
>  
>
I'm not too happy about the current combobox implementation; this should 
be rewritten in a menu/submenu or tree style.

>Also, I reported a bug in that if the return type is a table type
>pgadmin fails to include the schema when extracting the ddl.
>
>  
>
Nearly lost it... I added it to BUGS.txt now. We might need to refactor 
the code a little bit, that's why I don't fix it instantly.

Regards,
Andreas

Andreas Pflug wrote:
> Ian Freislich wrote:
> 
> >I can't find the pseudo-type RECORD in the drop-down list of return
> >types so it is impossible to create functions like "CREATE OR REPLACE
> >FUNCTION foo (TEXT) RETURNS SET OF RECORD AS..." using the gui.
> >  
> >
> Not all 'p' types are allowed as return types for functions, but 
> apparently the list was too restricted.
> I expanded the list (to be precise: allow all for certain exceptions), 
> committed to cvs.

Thanks.  That will fix all the niggles for us.

Beigin new to this list, is there a place where I could suggest
functionality (I have one nice to have request on low priority for
the developers which would make pgAdmin a TOAD equivalent for
PostgreSQL for us)?

Ian

Ian Freislich wrote:

>
>Beigin new to this list, is there a place where I could suggest
>functionality (I have one nice to have request on low priority for
>the developers which would make pgAdmin a TOAD equivalent for
>PostgreSQL for us)?
>  
>

Yes,
just post things like that on pgadmin-hackers.

Regards,
Andreas

Michael Shapiro wrote:

>>Actually it isn't random, but the sort order might not be what you 
>>expect. It looks like (regard as meta code)
>>ORDER BY isdomain, isArray, typname
>>    
>>
>
>This doens't mean anything to me. What I am after is to not have to
>scroll up and down searching for the type. If there is no order by clause
>that will suffice, 
>
There *is* an ordering clause, look at that pseudo code above.

>then you could add code in PgAdmin itself to do
>the ordering. What I think would be a good order is
>    builtin types first
>    user defined types next
>    schema table types next (ordered by schema)
>alphabetic within each group. Does that make sense?
>  
>

Michael,

please don't send private mail. Everything should be discussed publicly; 
we're Open Source. Besides, private mail screws up my inbox processing; 
your answers might well go unnoticed to spam.

As I said we'll be refactoring types handling. You might find find the 
current ordering not helpful, others might do so (at least I like 
domains first, because I use domains to abstract from real database 
types, increasing portability)

Regards,
Andreas