Re: Could someone help me fix my array_list function?

Would the same work for pg_user and pg_group?

It would be handy at times to easily check wether or not someone is member
of a group...
and since in pg_group the usernumbers are stored, one might need to do a
few lookups:
would it be hard to put such a thing in a view, or is that not-smart
thinking here?

I have to admit, arrays are still a bit hazy to me, in how to use them
properly in databases...
so I stick to the older solutions...

Michiel

At 15:27 20-1-2003 -0800, Joe Conway wrote:
>Guy Fraser wrote:
>>This is what I want to do:
>>select attribute,array_list(values,1,sizeof(values)) as value from av_list;
>>Turn :
>> attr6 | {val3,val7,val4,val5}
>>Into :
>> attr6 | val3
>> attr6 | val7
>> attr6 | val4
>> attr6 | val5
>
>You didn't mention the version of PostgreSQL. If you're using < 7.3, good
>luck ;-). If you are using 7.3, the following works:
>
>DROP TABLE mail_aliases;
>CREATE TABLE mail_aliases(
> a_mailbox text,
> a_destination text[]
>);
>
>INSERT INTO mail_aliases VALUES ('alias1', '{dest1}');
>INSERT INTO mail_aliases VALUES ('alias2', '{dest2,dest1}');
>INSERT INTO mail_aliases VALUES ('alias3', '{dest3,dest4}');
>INSERT INTO mail_aliases VALUES ('alias4', '{dest3,dest4,dest5}');
>INSERT INTO mail_aliases VALUES ('alias5', '{dest6,dest7}');
>INSERT INTO mail_aliases VALUES ('alias6', '{dest3,dest7,dest4,dest5}');
>
>CREATE TYPE mail_aliases_list_type AS (a_mailbox text, a_destination_el text);
>CREATE OR REPLACE FUNCTION mail_aliases_list() RETURNS SETOF
>mail_aliases_list_type AS '
>DECLARE
> rec record;
> retrec record;
> low int;
> high int;
>BEGIN
> FOR rec IN SELECT a_mailbox, a_destination FROM mail_aliases LOOP
> SELECT INTO low
>
>replace(split_part(array_dims(rec.a_destination),'':'',1),''['','''')::int;
> SELECT INTO high
>
>replace(split_part(array_dims(rec.a_destination),'':'',2),'']'','''')::int;
>
> FOR i IN low..high LOOP
> SELECT INTO retrec rec.a_mailbox, rec.a_destination[i];
> RETURN NEXT retrec;
> END LOOP;
> END LOOP;
> RETURN;
>END;
>' LANGUAGE 'plpgsql';
>
>regression=# SELECT a_mailbox, a_destination_el FROM mail_aliases_list();
> a_mailbox | a_destination_el
>-----------+------------------
> alias1 | dest1
> alias2 | dest2
> alias2 | dest1
> alias3 | dest3
> alias3 | dest4
> alias4 | dest3
> alias4 | dest4
> alias4 | dest5
> alias5 | dest6
> alias5 | dest7
> alias6 | dest3
> alias6 | dest7
> alias6 | dest4
> alias6 | dest5
>(14 rows)
>
>
>HTH,
>
>Joe
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org