Thread: PL/PGSQL for permutations?
I've found a number of basic references for PL/PGSQL, but am looking for something more complete. First question, is there available a *complete* reference for PL/PGSQL? I'm using PostgreSQL version 7.2.3-5.73 (for Redhat rpm packaging version), so it is version 7.2 I am concerned with information on (this is a default version, so at least on this machine, I'm not interested in updates in version). The situation that leads me to ask for this is a simple need to select two fields from a table, and generate a table that itself has two fields, populated by every permutation of unique values from the first table field pair. E.G., if I had in table 'one': left right ==== ===== a b a c b d ...then I'd need a list of a, b, c, d, and produce a new table: left right ==== ===== a b a c a d b a b c b d c a c b c d d a d b d c This isn't hard, but I'm struggling with PL/PGSQL data types. I'd love to just find a map or associative array type, and populate it from queries, sort it, and generate a new array or map from a nested loop. I'm not exactly sure how the PostgreSQL non-standard array type would work for this from PL/PGSQL, if at all. D. Stimits, stimits AT comcast DOT net
D. Stimits wrote: > table field pair. E.G., if I had in table 'one': > left right > ==== ===== > a b > a c > b d > > ...then I'd need a list of a, b, c, d, and produce a new table: > left right > ==== ===== > a b > a c > a d > b a > b c > b d > c a > c b > c d > d a > d b > d c I don't have 7.2 to test on (and you really should upgrade to 7.3.4 if possible anyway), but why not: create table t1(f1 text, f2 text); insert into t1 values('a','b'); insert into t1 values('a','c'); insert into t1 values('b','d '); select a, b from (select distinct f1 as a from t1 union select distinct f2 from t1) as ss1, (select distinct f1 as b from t1 union select distinct f2 from t1) as ss2 where ss1.a != ss2.b; a | b ----+---- a | b a | c a | d b | a b | c b | d c | a c | b c | d d | a d | b d | c (12 rows) HTH, Joe
Joe Conway wrote: > D. Stimits wrote: > > > table field pair. E.G., if I had in table 'one': > > left right > > ==== ===== > > a b > > a c > > b d > > > > ...then I'd need a list of a, b, c, d, and produce a new table: > > left right > > ==== ===== > > a b > > a c > > a d > > b a > > b c > > b d > > c a > > c b > > c d > > d a > > d b > > d c > > > I don't have 7.2 to test on (and you really should upgrade to 7.3.4 if > possible anyway), but why not: > > create table t1(f1 text, f2 text); > insert into t1 values('a','b'); > insert into t1 values('a','c'); > insert into t1 values('b','d '); > > select a, b > from > (select distinct f1 as a from t1 union select distinct f2 from t1) > as ss1, > (select distinct f1 as b from t1 union select distinct f2 from t1) > as ss2 > where ss1.a != ss2.b; > a | b > ----+---- > a | b > a | c > a | d > b | a > b | c > b | d > c | a > c | b > c | d > d | a > d | b > d | c > (12 rows) This worked quite well, thank you! I'm still in need though of learning more about PL/PGSQL, as I have other programming to add (well, I could do this in C as a PostgreSQL C function, but I want to stick to PL/PGSQL for the moment). I'm still looking for a non-trivial, in-depth, full reference to PL/PGSQL. I've found many good introductory or tutorial type web pages, but not a full and complete reference to PL/PGSQL. The permutations were themselves the easy part, now each permutation has to do some non-trivial combinatorics on trigger whenever a change is made. D. Stimits
D. Stimits wrote: > I'm still in need though of learning more about PL/PGSQL, as I have > other programming to add (well, I could do this in C as a PostgreSQL > C function, but I want to stick to PL/PGSQL for the moment). I'm > still looking for a non-trivial, in-depth, full reference to > PL/PGSQL. I've found many good introductory or tutorial type web > pages, but not a full and complete reference to PL/PGSQL. The > permutations were themselves the easy part, now each permutation has > to do some non-trivial combinatorics on trigger whenever a change is > made. > I can't point you to anything more than the online docs for PL/pgSQL, but if you're needing non-trivial statistics/math you should take a look at using PL/R instead (requires at least Postgres 7.3): http://www.joeconway.com/plr/ HTH, Joe