Osvaldo, That did the trick! Like you said, it had to do with the composite type. I added the column name to the row variable assignment and it works now. Thanks for the tip and thanks to everybody else for your assistance. Have a great day. Regards, Jeff Aycock -----Original Message----- From: Osvaldo Kussama [mailto:osvaldo.kussama@xxxxxxxxx] Sent: Wednesday, January 27, 2010 11:57 AM To: Aycock, Jeff R. Subject: Re: syntax error during function call 2010/1/27 Aycock, Jeff R. <JEFF.R.AYCOCK@xxxxxxxx>: > Hello, > > > > I have a function with three parameters that would populate a table in one > schema from another table of the same name in another schema. The tables > are dynamically selected at execution time. > > > > CREATE OR REPLACE FUNCTION schema_1.getAllSnapShot(user_id text, begin_dt > date, end_dt date) RETURNS SETOF schema_1.snapshot_table AS > > $BODY$ > > DECLARE > > r schema_1.snapshot_table%rowtype; > > > > BEGIN > > FOR r IN SELECT * FROM schema_1.snapshot_table r is a "composite type". http://www.postgresql.org/docs/current/interactive/rowtypes.html > > LOOP > > > > DECLARE whoami text := r; I believe you need use: r.column_name DECLARE whoami text := r.cloumn_name; > > > > BEGIN > > > > EXECUTE 'SELECT *, $1, now() INTO schema_1.'||whoami||' FROM > schema_2.'||whoami||' where created_dt between $2 and $3;' > > USING user_id, begin_dt, end_dt; > > > > END; > > RETURN NEXT r; > > END LOOP; > > RETURN; > > END > > $BODY$ > > LANGUAGE 'plpgsql' ; > > > > The snapshot_table has only one column for the table name. > > > > The function call would look like this: > > > > SELECT * FROM schema_1.getAllSnapShot('xyz9','2009-01-01','2010-01-01'); > > > > However, I get this error: > > > > ERROR: syntax error at or near "(" > > LINE 1: SELECT *, $1, now() INTO schema_1.(table_A) FROM schema_2.(table_A) > where created_dt between $2 and $3; > > > > I tried different escape characters for the row variable (whoami) but get > the same error as above. > > > > I also tried other approaches, including using "tabname::regclass" for the > table names but nothing seem to work. > > > > Any suggestion would be greatly appreciated. > Osvaldo -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
