Thread: Could someone help me fix my array_list function?

Could someone help me fix my array_list function?

From
Guy Fraser
Date:
Hi

I am trying to write a function to step through an array and output each value 
as a set {list}, I think.

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

Below I have included my functions, a test query, a table definition
and some sample data.

If anyone already has a function to do this I would be elated.

Note: text array_dims(array[]); function existed on the machine I started this 
on, but does not exist on my machine at home. It outputs a text value like 
'[1:1]' when there is only one item in the array and '[1:6]' when there is six 
items. My functions expect that function to exist.


Any help would be apreciated.

Guy

The entire selection below can be pasted to a shell, it will create a test 
database "testdb" add plpgsql to the database then create the functions, and a 
populated table before running a test query.

---%<...Cut Here...
createdb testdb
createlang plpgsql testdb
echo "
--###Start of Functions###
-- Array dimension functions.
--
-- Throw away old version of function
DROP FUNCTION array_diml(text[]);
--
-- Return the start 'left' dimension for the text array.
CREATE FUNCTION array_diml(text[])RETURNS int2AS
'select int2(ltrim(rtrim(rtrim(array_dims($1),\']012345679\'),\':\'),\'[\')) 
AS RESULT;'LANGUAGE sqlWITH (iscachable,isstrict)
;
--
-- Throw away old version of function
DROP FUNCTION array_dimr(text[]);
--
-- Return the end 'right' dimension for the text array.
CREATE FUNCTION array_dimr(text[])RETURNS int2AS 'select 
int2(rtrim(ltrim(ltrim(array_dims($1),\'[012345679\'),\':\'),\']\')) AS RESULT;'LANGUAGE sqlWITH (iscachable,isstrict)
;
--
-- Throw away old version of function
DROP FUNCTION array_list(text[],smallint);
--
-- Iterate array and post results
CREATE FUNCTION array_list(text[],smallint)
RETURNS SETOF text AS '
DECLARE  inarray ALIAS FOR $1;  dim ALIAS FOR $2;
BEGIN  FOR counter IN 1..dim LOOP
RAISE NOTICE ''Getting element % of %'',counter,inarray;  RETURN inarray[counter]; END LOOP;
END;
'
LANGUAGE 'plpgsql';
--###End of Functions###

--###Start of test query###
--
-- Get a list with each destination for each mailbox
SELECT a_mailbox,  array_list(a_destination,    array_dimr(a_destination))
FROM mail_aliases;
--###End of test query###

--###Start of table and sample data###
DROP TABLE mail_aliases;
CREATE TABLE mail_aliases(  a_mailbox text,  a_destination text[]
);
COPY mail_aliases FROM stdin USING DELIMITERS ':';
alias1:{dest1}
alias2:{dest2,dest1}
alias3:{dest3,dest4}
alias4:{dest3,dest4,dest5}
alias5:{dest6,dest7}
alias6:{dest3,dest7,dest4,dest5}
\.
--###End of table and sample data###

--###Start of test query###
--
-- Get a list with each destination for each mailbox
SELECT a_mailbox,  array_list(a_destination,    array_dimr(a_destination))
FROM mail_aliases;
--###End of test query###
" | psql testdb
---%<...Cut Here...




Re: Could someone help me fix my array_list function?

From
Joe Conway
Date:
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;
ENDLOOP; 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    |
dest4alias4    | dest3 alias4    | dest4 alias4    | dest5 alias5    | dest6 alias5    | dest7 alias6    | dest3 alias6
  | dest7 alias6    | dest4 alias6    | dest5
 
(14 rows)


HTH,

Joe



Re: Could someone help me fix my array_list function?

From
Michiel Lange
Date:
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




Re: Could someone help me fix my array_list function?

From
Joe Conway
Date:
Michiel Lange wrote:
> Would the same work for pg_user and pg_group?
> 

See:
http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=11378

With these groups:
regression=# select * from pg_group; groname | grosysid |    grolist
---------+----------+--------------- grp1    |      100 | {100,101,102} grp2    |      101 | {100,102}
(2 rows)

Output looks like:
regression=# select * from groupview; grosysid | groname | usesysid | usename
----------+---------+----------+---------      100 | grp1    |      100 | user1      100 | grp1    |      101 | user2
  100 | grp1    |      102 | user3      101 | grp2    |      100 | user1      101 | grp2    |      102 | user3
 
(5 rows)

Joe