|
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 DECLARE
whoami text := r; 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 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. Thanks, Jeff |
