hMU7Jr4t

1CREATE TABLE IF NOT EXISTS key_words(
2    id SERIAL PRIMARY KEY,
3    str_word varchar(100) NOT NULL,
4    active boolean NOT NULL,
5    insert_date timestamp NOT NULL,
6    number_times_selected bigint,
7    number_times_apear bigint
8);
9	
10CREATE OR REPLACE FUNCTION verify_key_words2 (character varying[], integer, out out_dat1 text, out out_dat2 text)
11RETURNS SETOF RECORD AS $$
12DECLARE 
13stringword text;
14integer_var int;
15i int;
16j int;
17k int;
18r record;
19rec record;
20sqlupdarray text;
21sqlinsarray text;
22insertarray character varying[];    
23updatearray character varying[];
24BEGIN
25j := 1;
26k := 1;
27FOR i IN 1..$2 LOOP
28    stringword := $1[i];
29    SELECT INTO r str_word FROM key_words WHERE LOWER(str_word) = LOWER(stringword);
30    GET DIAGNOSTICS integer_var = ROW_COUNT;
31    raise notice 'integer_var: %', integer_var;
32    IF integer_var <= 0 THEN
33        insertarray[j] := stringword;
34        j := j+1;
35        INSERT INTO key_words(str_word,active,insert_date,number_times_selected,number_times_apear) VALUES(stringword,false,CURRENT_TIMESTAMP,1,0);
36        raise notice 'INSERT: %', stringword;
37    ELSE 
38        updatearray[k] := stringword;
39        UPDATE key_words SET number_times_selected = number_times_selected + 1 WHERE str_word = stringword;
40        k := k+1;
41        raise notice 'UPDATE: %', stringword;
42    END IF;
43END LOOP;
44IF array_length(updatearray, 1) > 0 THEN
45    sqlupdarray := 'select ';
46    FOR i IN 1..array_length(updatearray, 1) LOOP
47        IF i > 1 THEN
48            sqlupdarray := sqlupdarray || ',';
49        END IF;
50        sqlupdarray := sqlupdarray || quote_literal(updatearray[i]);
51    END LOOP;
52    raise notice 'SQL UPDATE: %', sqlupdarray;
53    EXECUTE sqlupdarray INTO out_dat1;
54END IF;
55IF array_length(insertarray, 1) > 0 THEN
56    sqlinsarray := 'select ';
57    FOR i IN 1..array_length(insertarray, 1) LOOP
58        IF i > 1 THEN
59            sqlinsarray := sqlinsarray || ',';
60        END IF;
61        sqlinsarray := sqlinsarray || quote_literal(insertarray[i]);
62    END LOOP;
63    raise notice 'SQL INSERT: %', sqlinsarray;
64    EXECUTE sqlinsarray INTO out_dat2;
65END IF;
66return;
67END;
68$$  
69LANGUAGE plpgsql;
70	
71select * from verify_key_words2('{"dog"}',1);
72	
73NOTICE:  integer_var: 1
74NOTICE:  UPDATE: dog
75NOTICE:  SQL UPDATE: select 'dog'
76 out_dat1 | out_dat2 
77----------+----------
78(0 rows)