Thread: syntax error during function call
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
On 27/01/2010 15:40, Aycock, Jeff R. wrote: > BEGIN > > FOR r IN SELECT * FROM schema_1.snapshot_table > > LOOP > > > > DECLARE whoami text := r; I could be wrong, but I don't think that the DECLARE inside the loop is correct. I think you have to declare "whoami" with the rest of your variables in the DECLARE block at the top of the function, and then you can assign to it inside the loop. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
Raymond, I tried your suggestion but the result is the same when "whoami" is declared at the top of the function and assigned inside the loop. Thanks for the suggestion anyway. -----Original Message----- From: Raymond O'Donnell [mailto:rod@iol.ie] Sent: Wednesday, January 27, 2010 11:00 AM To: Aycock, Jeff R. Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] syntax error during function call On 27/01/2010 15:40, Aycock, Jeff R. wrote: > BEGIN > > FOR r IN SELECT * FROM schema_1.snapshot_table > > LOOP > > > > DECLARE whoami text := r; I could be wrong, but I don't think that the DECLARE inside the loop is correct. I think you have to declare "whoami" with the rest of your variables in the DECLARE block at the top of the function, and then you can assign to it inside the loop. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
>
> 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;
>
>
schema_1.(table_A) is nonsense. have to be "schema_1"."table_A"
regards
Pavel Stehule
Pavel,
Per your suggestion I modified one line below BEGIN to look like this:
EXECUTE 'SELECT *, $1, now() INTO "schema_1".'||"whoami"||' FROM
"schema_2".'||"whoami"||' where created_dt between $2 and $3;'
However, it is still giving me the same syntax error as before. I must be missing something here though.
Thanks for the suggestion, however.
Regards,
Jeff Aycock
-----Original Message-----
From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: Wednesday, January 27, 2010 11:13 AM
To: Aycock, Jeff R.
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] syntax error during function call
>
> 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;
>
>
schema_1.(table_A) is nonsense. have to be "schema_1"."table_A"
regards
Pavel Stehule
On 01/27/2010 08:27 AM, Aycock, Jeff R. wrote: > Pavel, > > Per your suggestion I modified one line below BEGIN to look like this: > > EXECUTE 'SELECT *, $1, now() INTO "schema_1".'||"whoami"||' FROM > "schema_2".'||"whoami"||' where created_dt between $2 and $3;' > > > However, it is still giving me the same syntax error as before. I must be missing something here though. On a hunch try CURRENT_TIMESTAMP instead of now(). > > Thanks for the suggestion, however. > > Regards, > Jeff Aycock > > -- Adrian Klaver adrian.klaver@gmail.com
Aycock, Jeff R. <JEFF.R.AYCOCK@saic.com> wrote:
> Pavel,
>
> Per your suggestion I modified one line below BEGIN to look like this:
>
> EXECUTE 'SELECT *, $1, now() INTO "schema_1".'||"whoami"||' FROM "schema_2".'||"whoami"||' where created_dt between
$2and $3;'
^^^^^^ ^^^^
How ist the correct table-name?
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Adrian, I tried that as well and got the same error result. Regards, Jeff Aycock -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@gmail.com] Sent: Wednesday, January 27, 2010 11:33 AM To: Aycock, Jeff R. Cc: Pavel Stehule; pgsql-general@postgresql.org Subject: Re: [GENERAL] syntax error during function call On 01/27/2010 08:27 AM, Aycock, Jeff R. wrote: > Pavel, > > Per your suggestion I modified one line below BEGIN to look like this: > > EXECUTE 'SELECT *, $1, now() INTO "schema_1".'||"whoami"||' FROM > "schema_2".'||"whoami"||' where created_dt between $2 and $3;' > > > However, it is still giving me the same syntax error as before. I must be missing something here though. On a hunch try CURRENT_TIMESTAMP instead of now(). > > Thanks for the suggestion, however. > > Regards, > Jeff Aycock > > -- Adrian Klaver adrian.klaver@gmail.com
On 01/27/2010 08:41 AM, Aycock, Jeff R. wrote:
> Adrian,
>
> I tried that as well and got the same error result.
>
> Regards,
> Jeff Aycock
>
>
I went back to the original function and assuming no cut/paste errors
there is a ';' missing after the last END.
END;
RETURN NEXT r;
END LOOP;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql' ;
--
Adrian Klaver
adrian.klaver@gmail.com
Thanks for the catch.
However, this did not fix the syntax error.
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
Sent: Wednesday, January 27, 2010 11:47 AM
To: Aycock, Jeff R.
Cc: Pavel Stehule; pgsql-general@postgresql.org
Subject: Re: [GENERAL] syntax error during function call
On 01/27/2010 08:41 AM, Aycock, Jeff R. wrote:
> Adrian,
>
> I tried that as well and got the same error result.
>
> Regards,
> Jeff Aycock
>
>
I went back to the original function and assuming no cut/paste errors
there is a ';' missing after the last END.
END;
RETURN NEXT r;
END LOOP;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql' ;
--
Adrian Klaver
adrian.klaver@gmail.com
On 01/27/2010 08:50 AM, Aycock, Jeff R. wrote: > Thanks for the catch. > > However, this did not fix the syntax error. > > You are sure the function is being replaced with versions that have the changes? In other words does \df+ show the changes? -- Adrian Klaver adrian.klaver@gmail.com
On 01/27/2010 08:50 AM, Aycock, Jeff R. wrote: > Thanks for the catch. > > However, this did not fix the syntax error. > Looking back at this thread I second Andreas's suggestion. It seems the syntax is right but the names are wrong. What is the result when you do SELECT * FROM schema_1.snapshot_table? -- Adrian Klaver adrian.klaver@gmail.com
On Wed, Jan 27, 2010 at 10:40:17AM -0500, Aycock, Jeff R. wrote: > EXECUTE 'SELECT *, $1, now() INTO schema_1.'||whoami||' FROM schema_2.'||whoami||' where created_dt between $2 and $3;' You'll also need to expand those other parameters. The code is executed in an independent scope and hence PG doesn't know what $1, $2 or $3 are. The builtin function "quote_literal" is probably best to use here, especially for the TEXT type. -- Sam http://samason.me.uk/
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 assignmentand 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@gmail.com] Sent: Wednesday, January 27, 2010 11:57 AM To: Aycock, Jeff R. Subject: Re: [GENERAL] syntax error during function call 2010/1/27 Aycock, Jeff R. <JEFF.R.AYCOCK@saic.com>: > 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