Here's what I'm doing.
It is to work on existing tables (not triggerable), but for subsequent updates to the table(s) that I'm tokenizing fields for, a trigger will be used to do the tokenizing of new data
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
CREATE OR REPLACE FUNCTION gen_tokens_n_map(character varying, character varying, varchar, bigint, bigint)
RETURNS void AS
'
-- Sample invocation: SELECT gen_tokens_n_map(^businesscontact^,^businessid^,^publicname^, 0, 10000) ;
-- Where ^ are actually apostrophes.
DECLARE vTableName ALIAS FOR $1 ;
KeyColName ALIAS FOR $2 ;
ValueColName ALIAS FOR $3 ;
offsetval ALIAS FOR $4 ;
limitval ALIAS FOR $5 ;
TableFieldVal INTEGER ;
TableRec RECORD ;
BusID BIGINT ;
.
.
.
ExecuteString := ''CREATE TABLE temp_gentokenstable (ID, ValueCol) AS '' ;
ExecuteString := ExecuteString || '' SELECT '' || KeyColName || '', '' || ValueColName ;
ExecuteString := ExecuteString || '' FROM '' || vTableName || '' ORDER BY '' || KeyColName ;
ExecuteString := ExecuteString || '' OFFSET '' || offsetval::text::varchar || '' LIMIT '' || limitval::text::varchar || '' ;'';
EXECUTE ExecuteString ;
FOR TableRec IN SELECT * FROM temp_gentokenstable order by id LOOP
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
I will now try Pavel's suggestion of
use a EXECUTE statement and FOR IN EXECUTE statement
And to DJ's question, version 7.4.12. Yes, OUCH!
I'll let you know if the execute in the FOR LOOP will help out.
Thanks all!
Ralph
-- ===============================================================================================
FOR TableRec IN SELECT * FROM temp_gentokenstable order by id LOOP
--
Ralph
_________________________
