Re: Help with a view - Mailing list pgsql-sql

> Basically, what I've got is the first person and the tag_type. I can do
> it with a function from PHP:
>
> function get_spouses($p) {
>     $handle = pg_query("select person from principals
>                        where event in (select event from principals
>                        where person = $p and tag_type = " .MARR. ")
>                        order by sort_date");

What about adding an extra condition ' and person <> $p ?
                      select person from principals as a                       where event in (select event from
principalsas b                       where person = $p and tag_type = " .MARR. ")                       and  a.person
<>$p                       order by sort_date 

>pgslekt=> select * from principals where event=15821;
>person | event | place | sort_date  | tag_type
>--------+-------+-------+------------+----------
>   2 | 15821 |  1152 | 1999-09-17 |        4
>   3 | 15821 |  1152 | 1999-09-17 |        4
(2 rows)
>I'd also like a view that gives me the two rows combined into one, with
>"person" replaced by p1 and p2.

might consider grouping by all other columns except person and use an
aggregate function over person ,


CREATE VIEW blah AS select list(person) as persons , event , place ,
sort_date , tag_type from principals group by event , place ,
sort_date , tag_type ;

(list is a custom function in my database , currently not posted)

not sure though if i got your problem exactly.

regds
mallah.



>     $i=0;
>     $spouses = array();
>     while ($row = pg_fetch_row($handle) {
>         if $row[0] != $p
>              $spouses[$i++] = $row[0];
>     }
>     return $spouses;
> }