Обсуждение: Error >>syntax error<< at >>$1<< at character 53

Hi,

I try to code a PL/pgSQL function, but always get to an execution error, I am
unable to solve:

sledger=# \i fnc_summary.pgsql
CREATE FUNCTION
COMMENT
GRANT
COMMIT
sledger=# select * from GET_SUMMARY();
ERROR:  Error >>syntax error<< at >>$1<< at character 53
CONTEXT:  PL/pgSQL function "get_summary" line 9 at for over select rows


The function script is as follows:

-- $Revision$
-- $Name$
-- $Date$
-- $Author$
-- $State$
-- $Locker$
-- $Source$
-- $Header$

-- Copyright (C) 2004 Thiemo Kellner

-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.

-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.

-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

-- http://www.gnu.org/licenses/gpl.html


--\set SINGLESTEP
\unset SINGLESTEP
\set AUTOCOMMIT off

-- Objekte löschen
\unset ON_ERROR_STOP
--drop function GET_SUMMARY();


-- Objekte anlegen
\set ON_ERROR_STOP
create or replace function GET_SUMMARY() returns integer as '
   declare
      STR_SOURCE_NAME         constant pg_catalog.pg_views.viewname%type
                                    := \'BUCHUNGEN\';
      STR_OBJEKTVIEW          constant pg_catalog.pg_views.viewname%type
                                    := \'OBJEKTHIERARCHIE\';
      R_OBJEKTHIERARCHIE      record;
      TXT                     text;
   begin
      for R_OBJEKTHIERARCHIE in
         select
               OBJEKTKATEGORIE,
               OBJEKTGRUPPE,
               OBJEKT
            from
               STR_OBJEKTVIEW
      loop
         COMMON.LOG_INFO(\'Objektkategorie ist\' ||
             R_OBJEKTHIERARCHIE.OBJEKTKATEGORIE)
         ;
      end loop;
      TXT := \'STR_SOURCE_NAME: \' || STR_SOURCE_NAME;
      perform common.log_debug(TXT);
      return 0;
   end;
' language plpgsql;


comment on function GET_SUMMARY() is '

';

grant execute on function GET_SUMMARY() to public;

commit;


--\i include_a_file.pgsql

-- $Log$


Does somebody have an idea how to fix the function?

Cheers

Thiemo

--
root ist die Wurzel allen �bels

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