Thread: syntax error during function call

syntax error during function call

From
"Aycock, Jeff R."
Date:

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

 

Re: syntax error during function call

From
Raymond O'Donnell
Date:
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

Re: syntax error during function call

From
"Aycock, Jeff R."
Date:
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

Re: syntax error during function call

From
Pavel Stehule
Date:
>
> 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

Re: syntax error during function call

From
"Aycock, Jeff R."
Date:
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

Re: syntax error during function call

From
Adrian Klaver
Date:
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

Re: syntax error during function call

From
Andreas Kretschmer
Date:
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°

Re: syntax error during function call

From
"Aycock, Jeff R."
Date:
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

Re: syntax error during function call

From
Adrian Klaver
Date:
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

Re: syntax error during function call

From
"Aycock, Jeff R."
Date:
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

Re: syntax error during function call

From
Adrian Klaver
Date:
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

Re: syntax error during function call

From
Adrian Klaver
Date:
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

Re: syntax error during function call

From
Sam Mason
Date:
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/

Re: syntax error during function call

From
"Aycock, Jeff R."
Date:
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