Greetings, I'm trying to create a função em postgresql
that allows me to check if a word or a set of words exists in a database.
To achieve this, I am using a table that holds the keywords.
My table:
CREATE TABLE IF NOT EXISTS key_words(
id SERIAL PRIMARY KEY,
str_word varchar(100) NOT NULL,
active boolean NOT NULL,
insert_date timestamp NOT NULL,
number_times_selected bigint,
number_times_apear bigint
);
I use a function that allows me to perform an update or insert new values according to key words that already exist or not in the database. This function should return words as they exist or not in the database, in order to perform other functions.
My role:
CREATE OR REPLACE FUNCTION verify_key_words2 (character varying[], integer, out out_dat1 text, out out_dat2 text)
RETURNS SETOF RECORD AS $$
DECLARE
stringword text;
integer_var int;
i int;
j int;
k int;
r record;
rec record;
sqlupdarray text;
sqlinsarray text;
insertarray character varying[];
updatearray character varying[];
BEGIN
j := 1;
k := 1;
FOR i IN 1..$2 LOOP
stringword := $1[i];
SELECT INTO r str_word FROM key_words WHERE LOWER(str_word) = LOWER(stringword);
GET DIAGNOSTICS integer_var = ROW_COUNT;
raise notice 'integer_var: %', integer_var;
IF integer_var <= 0 THEN
insertarray[j] := stringword;
j := j+1;
INSERT INTO key_words(str_word,active,insert_date,number_times_selected,number_times_apear) VALUES(stringword,false,CURRENT_TIMESTAMP,1,0);
raise notice 'INSERT: %', stringword;
ELSE
updatearray[k] := stringword;
UPDATE key_words SET number_times_selected = number_times_selected + 1 WHERE str_word = stringword;
k := k+1;
raise notice 'UPDATE: %', stringword;
END IF;
END LOOP;
IF array_length(updatearray, 1) > 0 THEN
sqlupdarray := 'select ';
FOR i IN 1..array_length(updatearray, 1) LOOP
IF i > 1 THEN
sqlupdarray := sqlupdarray || ',';
END IF;
sqlupdarray := sqlupdarray || quote_literal(updatearray[i]);
END LOOP;
raise notice 'SQL UPDATE: %', sqlupdarray;
EXECUTE sqlupdarray INTO out_dat1;
END IF;
IF array_length(insertarray, 1) > 0 THEN
sqlinsarray := 'select ';
FOR i IN 1..array_length(insertarray, 1) LOOP
IF i > 1 THEN
sqlinsarray := sqlinsarray || ',';
END IF;
sqlinsarray := sqlinsarray || quote_literal(insertarray[i]);
END LOOP;
raise notice 'SQL INSERT: %', sqlinsarray;
EXECUTE sqlinsarray INTO out_dat2;
END IF;
return;
END;
$$
LANGUAGE plpgsql;
Call the function:
select * from verify_key_words2('{"dog"}',1);
When I call my function on the postgres command line I get the following output:
NOTICE: integer_var: 1
NOTICE: UPDATE: dog
NOTICE: SQL UPDATE: select 'dog'
out_dat1 | out_dat2
----------+----------
(0 rows)
I have tried other solutions as an example:
- Postgres 'data types';
- Return an array directly;
- using the example link .
The first (Postgres 'data types') though it works, have a problem of having to fix the number of values I can return.
The second (Postgres 'return array') I can not use because I'm calling the function from c ++ using libqxx
, which means I can not recognize the array
returned.
The third gives an error that does not recognize the type of output
when return record
.
What am I doing wrong in my function so that it does not return the data?
Thank you in advance for your willingness to help!