Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Unanswered: Function with variables/insert/for loop problems

    I am using DreamCoder as my front end to my PostgreSQL database. It has an import feature into the database that is very helpful. I have created a function that is called inside the after_import function.

    The function needs to call distinct names in the name table and loop through them. When the name = x, all the entries in my task table with the name x and sent to that person's task table, which is named x.

    I know this is confusing but I am having serious errors and since I am new to PostgreSQL any help would be appreciated.

    Here is my function:

    Code:
    CREATE OR REPLACE FUNCTION table_name()
      RETURNS text AS
    $BODY$
    DECLARE
         table_name TEXT;
    BEGIN
    
        FOR table_name IN SELECT DISTINCT "Name_ITable" FROM "import_to_Task"
        LOOP
            EXECUTE 'INSERT INTO ' || table_name || ' ("Task_TTable") (SELECT DISTINCT "import_to_Task"."Task_ITable" FROM "import_to_Task" WHERE "Task_ITable" <> ALL (SELECT ' || table_name ||'."Task_TTable" FROM ' || table_name || '));';
        END LOOP;
    RETURN NULL;
    
    END
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100;
    ALTER FUNCTION new_name()
      OWNER TO postgres;
    The error that I am getting is that the column "x" does not exist (where table_name = x). So, I need to reference the text 'x' but it is coming out just x (no apostrophes).

    Thanks in advance for any help!

  2. #2
    Join Date
    Apr 2012
    Posts
    2

    Fixed it

    Had to use quote_literal(table_name)... now it works!!

Tags for this Thread

Posting Permissions

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