Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Unanswered: Creating a Dynamic Update Statement

    My basic goal is to update specific fields in an existing table based on a table that I have loaded with data. Here is what I am doing

    1. drop table v_import; -- drops old version of the table

    2. create table v_import as select x, y, z from v_test where 1-==0; --creates empty table with some of the columns from the table I am gong to update.

    3. COPY v_import
    (x, y, z)
    from E'\\temp\\data.csv' WITH NULL AS ' ' DELIMITER ',' CSV HEADER; --loades the data into the temp table.

    =====UP TO THIS POINT EVERYTHING IS OK=====
    I then run the bellow function and get this error: ERROR: missing FROM-clause entry for table "voyrec". I think if I can execute the last update statement from the string I have created I will be good but it is not working. This is the function:

    -- Function: import()

    -- DROP FUNCTION import();

    CREATE OR REPLACE FUNCTION import()
    RETURNS boolean AS
    $BODY$
    DECLARE



    --Variables used to build the SET clause
    setRec information_schema.columns%rowtype; --container for record set
    setField character varying; --variable for each field
    setClause character varying =' '; --set clause for each query

    --Variables used to update voyaged table
    voyRec voyages%rowtype;
    voyID integer;
    upQ character varying =''; --Query used to update table


    BEGIN
    RAISE NOTICE '----------START----------';




    --Constructs SET clause by querying list of columns in v_import table
    FOR setRec IN SELECT * FROM information_schema.columns WHERE table_name ='v_import' AND column_name != 'voyageid'
    LOOP
    setField := setRec.column_name;
    setClause := setClause ||' ' || setField || ' = ' || 'voyRec.' || setField || ' ,';
    END LOOP;

    --Remove last comma
    setClause := rtrim(setClause, ',');
    RAISE NOTICE 'SET CLAUSE: %', setClause;



    --Update voyages table
    FOR voyRec IN SELECT * FROM v_import
    LOOP
    voyID:=voyRec.voyageid;
    RAISE NOTICE 'Processing voyageId: %', voyID;
    EXECUTE 'UPDATE v_test SET ' || setClause || ' WHERE revision = 1 AND suggestion = ''f'' AND voyageid = ' || voyID;
    END LOOP;



    RAISE NOTICE '---------END----------';

    RETURN true;

    END;
    $BODY$
    LANGUAGE 'plpgsql' VOLATILE
    COST 100;
    ALTER FUNCTION import() OWNER TO tast;

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Are you only importing/updating one table or are there many? It seems like a very inefficient (slow) way to do it unless you've got hundreds of tables.

  3. #3
    Join Date
    Nov 2009
    Posts
    2
    Only one table.

    If you know if a better way I am willing to try it.

    The only problem is that there are key constraints with other tables so I want to only touch certian fiels.

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Here is a blog about how I do this type of thing. Using SQL and views to determine which rows to insert, update and delete.
    In-database ETL Flex and Specs()

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •