From: | Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> |
---|---|
To: | PM Support <PM_Support(at)fairfieldtech(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Function with variables/insert/for loop problems |
Date: | 2012-04-05 00:16:25 |
Message-ID: | 4F7CE459.7060308@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 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!
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(dot)klaver(at)gmail(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Gauthier, Dave | 2012-04-05 02:26:00 | v8.3.4 metadata changes while users active |
Previous Message | iihero | 2012-04-05 00:07:25 | Re: Function with variables/insert/for loop problems |