Re: Catalogs design question - Mailing list pgsql-hackers

Hello Haller!!
>
> Your question about  - pg_proc
> select t.typname from pg_type t , pg_proc p
> where p.proname = '<your_stored_procedure>' and p.proargtypes[0] = t.oid ;
> select t.typname from pg_type t , pg_proc p
> where p.proname = '<your_stored_procedure>' and p.proargtypes[1] = t.oid ;
> ...
> select t.typname from pg_type t , pg_proc p
> where p.proname = '<your_stored_procedure>' and p.proargtypes[7] = t.oid ;
>
> As far as I understand the proargtypes entries 0 means no further
parameter.
> This oidvector type of proargtypes seems to have a start index of 0.
> As long as there are at maximum 8 parameters allowed, this looks
practicable.
There is such a limit ? I didn't know. This makes your code a working way.
I'll look further on this later... and even if it's not a query that I would
say it's beautiful, it's a way, thanks :).

> Your question about  - pg_group
> The pg_group column is more bulky, because the int4[] type does not have
> an upper limit.
> So, the only solution I can see is
> get the number of array elements of the group you want to query
> select array_dims(grolist) from pg_group where groname = '<your_group>';
>
> and then generate automatically a query like
>
> select u.usename from pg_user u , pg_group g where
>  g.grolist[1] = u.usesysid and g.groname='<your_group>'
> union
> select u.usename from pg_user u , pg_group g where
>  g.grolist[2] = u.usesysid and g.groname='<your_group>'
> union
> ...
> select u.usename from pg_user u , pg_group g where
>  g.grolist[n] = u.usesysid and g.groname='<your_group>' ;
>
> This looks very much like another crude hack you've already
> complained about. Sorry, but I can't help.
Yes, it's ugly code. I would rather write a function, but again I can't
assume the user has pl/perl or pl/pgsql (or any other).

> Two more items I do not understand:
> You said, the procedures to search arrays in contrib/ are slow.
> Maybe that's true, but usually you do not have thousands of users
> in a group, don't you.
Yes. I would use it if I can.
> You said, many users cannot compile this contrib code. Yes, and they
> are not supposed to do so, because it's up to a system admin to do.
> What do I miss here?
Oh, I develop an interface for PostgreSQL called
pgExpress(http://www.vitavoom.com) - it's like an ODBC driver or such. I
must provide the functionality I described for the driver users; it's not
for me. I would of course have compiled and used the contrib code. But the
driver must work "out-of-the-box", and requiring a recompile (where many
times is impossible to users) is not a solution...
Right now, I'm hardcoding that relation inside the driver, what's also not
what I dreamed about, but I seem to have no other choice.

Thanks for the ideas btw :)

Best Regards,
Steve Howe