Re: Function with variables/insert/for loop problems

["iihero" <iihero@xxxxxx>]

How about ...  "' || table_name || '" ...? 

------------------

~~~~~~~~~~~~~~~~~~~~~~~~~

iihero

http://www.sql9.com

http://www.sql6.com
~~~~~~~~~~~~~~~~~~~~~~~~~
 

------------------ Original ------------------

From:  "PM Support"<PM_Support@xxxxxxxxxxxxxxxxx>;

Date:  Thu, Apr 5, 2012 03:10 AM

To:  "pgsql-general@xxxxxxxxxxxxxx"<pgsql-general@xxxxxxxxxxxxxx>;

Subject:  [GENERAL] 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 import_to_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:

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!

Kaitlin
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general