Re: Error >>syntax error<< at >>$1<< at character 53

Thiemo Kellner <thiemo@thiam.ch> writes:
> I try to code a PL/pgSQL function, but always get to an execution error, I =
> am=20
> unable to solve:

> create or replace function GET_SUMMARY() returns integer as '
>    declare
>       STR_SOURCE_NAME         constant pg_catalog.pg_views.viewname%type
>                                     :=3D \'BUCHUNGEN\';
>       STR_OBJEKTVIEW          constant pg_catalog.pg_views.viewname%type
        ^^^^^^^^^^^^^^
>                                     :=3D \'OBJEKTHIERARCHIE\';
>       R_OBJEKTHIERARCHIE      record;
>       TXT                     text;
>    begin
>       for R_OBJEKTHIERARCHIE in
>          select
>                OBJEKTKATEGORIE,
>                OBJEKTGRUPPE,
>                OBJEKT
>             from
>                STR_OBJEKTVIEW
                 ^^^^^^^^^^^^^^
>       loop

Don't use plpgsql variables named the same as tables or fields that you
need to reference in queries inside the function.  plpgsql will think it
ought to substitute the variable value as a parameter.

8.0devel generates a more useful error message from this function:

regression=# select get_summary();
ERROR:  syntax error at or near "$1" at character 53
QUERY:   select OBJEKTKATEGORIE, OBJEKTGRUPPE, OBJEKT from  $1
CONTEXT:  PL/pgSQL function "get_summary" line 9 at for over select rows
LINE 1:  select OBJEKTKATEGORIE, OBJEKTGRUPPE, OBJEKT from  $1
                                                            ^

Here you can actually see the query string fed to the SQL engine,
and see that STR_OBJEKTVIEW got replaced by a parameter symbol,
which of course is not going to work.

            regards, tom lane