pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCould someone help me fix my array_list function?
4+ messages / 3 participants
[nested] [flat]
* Could someone help me fix my array_list function?
@ 2003-01-20 18:50 Guy Fraser <guy@incentre.net>
2003-01-20 23:27 ` Re: Could someone help me fix my array_list function? Joe Conway <mail@joeconway.com>
2003-01-21 00:38 ` Re: Could someone help me fix my array_list function? Michiel Lange <michiel@minas.demon.nl>
0 siblings, 2 replies; 4+ messages in thread
From: Guy Fraser @ 2003-01-20 18:50 UTC (permalink / raw)
To: pgsql-sql
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 int2
AS
'select int2(ltrim(rtrim(rtrim(array_dims($1),\']012345679\'),\':\'),\'[\'))
AS RESULT;'
LANGUAGE sql
WITH (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 int2
AS 'select
int2(rtrim(ltrim(ltrim(array_dims($1),\'[012345679\'),\':\'),\']\')) AS RESULT;'
LANGUAGE sql
WITH (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...
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Could someone help me fix my array_list function?
2003-01-20 18:50 Could someone help me fix my array_list function? Guy Fraser <guy@incentre.net>
@ 2003-01-20 23:27 ` Joe Conway <mail@joeconway.com>
1 sibling, 0 replies; 4+ messages in thread
From: Joe Conway @ 2003-01-20 23:27 UTC (permalink / raw)
To: Guy Fraser <guy@incentre.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Could someone help me fix my array_list function?
2003-01-20 18:50 Could someone help me fix my array_list function? Guy Fraser <guy@incentre.net>
@ 2003-01-21 00:38 ` Michiel Lange <michiel@minas.demon.nl>
2003-01-21 01:45 ` Re: Could someone help me fix my array_list function? Joe Conway <mail@joeconway.com>
1 sibling, 1 reply; 4+ messages in thread
From: Michiel Lange @ 2003-01-21 00:38 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Guy Fraser <guy@incentre.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Could someone help me fix my array_list function?
2003-01-20 18:50 Could someone help me fix my array_list function? Guy Fraser <guy@incentre.net>
2003-01-21 00:38 ` Re: Could someone help me fix my array_list function? Michiel Lange <michiel@minas.demon.nl>
@ 2003-01-21 01:45 ` Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 4+ messages in thread
From: Joe Conway @ 2003-01-21 01:45 UTC (permalink / raw)
To: Michiel Lange <michiel@minas.demon.nl>; +Cc: Guy Fraser <guy@incentre.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-01-21 01:45 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-20 18:50 Could someone help me fix my array_list function? Guy Fraser <guy@incentre.net>
2003-01-20 23:27 ` Joe Conway <mail@joeconway.com>
2003-01-21 00:38 ` Michiel Lange <michiel@minas.demon.nl>
2003-01-21 01:45 ` Joe Conway <mail@joeconway.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox