From: | "Aycock, Jeff R(dot)" <JEFF(dot)R(dot)AYCOCK(at)saic(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | syntax error during function call |
Date: | 2010-01-27 15:40:17 |
Message-ID: | 65151B9A7C8C9C4284ABC85F0336DE5003972CE6@0015-its-exmb11.us.saic.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
LOOP
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 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.
Thanks,
Jeff
From | Date | Subject | |
---|---|---|---|
Next Message | Igor Neyman | 2010-01-27 15:40:51 | relation between records in "main" and "toast" tables |
Previous Message | Pavel Stehule | 2010-01-27 15:35:25 | Re: Variadic polymorpic functions |