jJhhJHpi

1CREATE TABLE myTable
2(
3  someKey integer,
4  someBool boolean
5);
6
7insert into myTable values (1, 't'),(1, 't'),(2, 'f'),(2, 't');
8	
9CREATE FUNCTION do_and(int4) RETURNS boolean AS
10$func$
11declare
12    rec record;
13    retVal boolean = 't'; -- necessary, or true is returned as null (it's weird)
14begin
15    if not exists (select someKey from myTable where someKey = $1) then
16    	return null; -- and because we had to initialise retVal, if no rows are     found true would be returned
17    end if;
18
19    for rec in select someBool from myTable where someKey = $1 loop
20    	retVal := rec.someBool AND retVal;
21    end loop;
22
23    return retVal;
24end;
25$func$ LANGUAGE 'plpgsql' VOLATILE;
26	
27select do_and(1) => t
28select do_and(2) => f
29select do_and(3) => null
30	
31select bool_and(someBool)
32  from myTable
33  where someKey = $1
34  group by someKey;
35	
36SELECT someKey, 
37  CASE WHEN sum(CASE WHEN someBool THEN 1 ELSE 0 END) = count(*)
38                    THEN true 
39                    ELSE false END as boolResult
40FROM  table
41GROUP BY someKey
42	
43return_value = NULL
44
45IF EXISTS
46(
47     SELECT
48          *
49     FROM
50          My_Table
51     WHERE
52          some_key = $1
53)
54BEGIN
55     IF EXISTS
56     (
57          SELECT
58               *
59          FROM
60               My_Table
61          WHERE
62               some_key = $1 AND
63               some_bool = 'f'
64     )
65          SELECT return_value = 'f'
66     ELSE
67          SELECT return_value = 't'
68END
69	
70select foo1.count_key_items = foo2.count_key_true_items
71from
72   (select count(someBool) as count_all_items from myTable where someKey = '1') as foo1,
73   (select count(someBool) as count_key_true_items from myTable where someKey = '1' and someBool) as foo2
74	
75CREATE FUNCTION do_and(int4)
76  RETURNS boolean AS
77$BODY$
78  SELECT
79    MAX(bar)::bool
80  FROM (
81    SELECT
82      someKey,
83      MIN(someBool::int) AS bar
84    FROM
85      myTable
86    WHERE
87      someKey=$1
88    GROUP BY
89      someKey
90
91    UNION
92
93    SELECT
94      $1,
95      NULL
96  ) AS foo;
97$BODY$
98  LANGUAGE 'sql' STABLE;
99	
100SELECT
101  someKey,
102  MIN(someBool::int)::bool AS bar
103FROM
104  myTable
105WHERE
106  someKey=$1
107GROUP BY
108  someKey
109	
110CREATE FUNCTION do_and(key int) RETURNS boolean
111  STABLE LANGUAGE 'plpgsql' AS $$
112DECLARE
113  v_selector CURSOR(cv_key int) FOR
114    SELECT someBool FROM myTable WHERE someKey = cv_key;
115  v_result boolean;
116  v_next boolean;
117BEGIN
118  OPEN v_selector(key);
119  LOOP
120    FETCH v_selector INTO v_next;
121    IF not FOUND THEN
122      EXIT;
123    END IF;
124    IF v_next = false THEN
125      v_result := false;
126      EXIT;
127    END IF;
128    v_result := true;
129  END LOOP;
130  CLOSE v_selector;
131  RETURN v_result;
132END
133$$;
134	
135SELECT  DISTINCT ON (someKey) someKey, someBool
136FROM    myTable m
137ORDER BY
138        someKey, someBool NULLS FIRST
139	
140SELECT  DISTINCT ON (someKey) someKey, someBool
141FROM    myTable m
142ORDER BY
143        someKey, someBool DESC NULLS FIRST