Thread: Re ==> EXECUTE of a 'create table' string is not happening

Re ==> EXECUTE of a 'create table' string is not happening

From

Ralph Smith

Date:

22 February 2011, 18:48:34

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
_________________________