Re: syntax error during function call

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