Re: Suggestion on "External types" sql query in pgAdmin3

Hi

On Mon, Mar 21, 2016 at 10:55 AM, Murtuza Zabuawala
<murtuza(dot)zabuawala(at)enterprisedb(dot)com> wrote:
> Hi All,
>
> Below is the query to fetch Input/Output/Send/Receive/Analyze functions
> while creating new external type (create mode) in pgAdmin3,
>
> With latest git pull [File: pgadmin/dlg/dlgType.cpp] [Line: 548],
>
> Current:
> SELECT proname, nspname
> FROM (
> SELECT proname, nspname, max(proargtypes[0]) AS arg0, max(proargtypes[1]) AS
> arg1
> FROM pg_proc p
> JOIN pg_namespace n ON n.oid=pronamespace
> GROUP BY proname, nspname
> HAVING count(proname) = 1 ) AS uniquefunc
> WHERE arg0 <> 0 AND arg1 = 0;
>
> Modified:
> SELECT proname, nspname
> FROM (
> SELECT proname, nspname, max(proargtypes[0]) AS arg0, max(proargtypes[1]) AS
> arg1
> FROM pg_proc p
> JOIN pg_namespace n ON n.oid=pronamespace
> GROUP BY proname, nspname
> HAVING count(proname) = 1 ) AS uniquefunc
> WHERE arg0 <> 0 AND arg1 IS NULL;
>
>
> When I changed where condition to 'arg1 IS NULL' from 'arg1 = 0', All combo
> boxes are populated.
>
> Can someone please confirm if it's correct?

I'm not sure it's that simple is it? The docs say:

- The input function can be declared as taking one argument of type
cstring, or as taking three arguments of types cstring, oid, integer.

- The output function must be declared as taking one argument of the
new data type. The output function must return type cstring.

- The receive function can be declared as taking one argument of type
internal, or as taking three arguments of types internal, oid,
integer.

- The send function must be declared as taking one argument of the new
data type. The send function must return type bytea.

- The analysis function must be declared to take a single argument of
type internal, and return a boolean result.

Unless it's trivial, please don't spend time trying to fix this in
pgAdmin 3. Clearly noone has run into it in years, so I'd rather
concentrate on getting it right for pgAdmin 4.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company