Re: 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(at)iol(dot)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(at)iol(dot)ie]
Sent: Wednesday, January 27, 2010 11:00 AM
To: Aycock, Jeff R.
Cc: pgsql-general(at)postgresql(dot)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(at)iol(dot)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(dot)stehule(at)gmail(dot)com]
Sent: Wednesday, January 27, 2010 11:13 AM
To: Aycock, Jeff R.
Cc: pgsql-general(at)postgresql(dot)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(dot)klaver(at)gmail(dot)com

Aycock, Jeff R. <JEFF(dot)R(dot)AYCOCK(at)saic(dot)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 $2 and $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(dot)klaver(at)gmail(dot)com]
Sent: Wednesday, January 27, 2010 11:33 AM
To: Aycock, Jeff R.
Cc: Pavel Stehule; pgsql-general(at)postgresql(dot)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(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)com

Thanks for the catch.

However, this did not fix the syntax error.

-----Original Message-----
From: Adrian Klaver [mailto:adrian(dot)klaver(at)gmail(dot)com]
Sent: Wednesday, January 27, 2010 11:47 AM
To: Aycock, Jeff R.
Cc: Pavel Stehule; pgsql-general(at)postgresql(dot)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(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)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 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(dot)kussama(at)gmail(dot)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(dot)R(dot)AYCOCK(at)saic(dot)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