Thread: 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
veryhelpful. 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
inmy 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

On 04/04/2012 12:10 PM, PM Support wrote:
> I am using DreamCoder as my front end to my PostgreSQL database. It has an import feature into the database that is
veryhelpful. 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
inmy 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!

See here:
http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html

Example 39-1. Quoting values in dynamic queries

>
> Kaitlin


--
Adrian Klaver
adrian.klaver@gmail.com