how retrive this problem

[rajeev85]

 
CREATE OR REPLACE FUNCTION schema._pg_object_getobjectlist(varchar) 
RETURNS refcursor AS 
'
DECLARE
    columnid int4;
    search_string varchar;
    temp refcursor;
    rc refcursor;
BEGIN
    search_string :=  \'%\' || $1 || \'%\'; 
 
    ----------------------------------------------------------------------------
    -- clean-up temporary object list table before populating it with data
    ----------------------------------------------------------------------------
 
    DELETE FROM schema.tempobjectlist;
 
    OPEN temp FOR SELECT  idobject  FROM  schema.strnodeprops  
    WHERE  spropertyvalue  LIKE search_string; 
 
    LOOP
        FETCH temp INTO columnid;
        EXIT WHEN NOT FOUND;
        PERFORM schema._pg_object_utility(columnid);
    END LOOP;
 
    CLOSE temp;
 
    ----------------------------------------------------------------------------
    -- make up a reference to the resulting table and return it to an external 
    --                              program
    ----------------------------------------------------------------------------
 
    OPEN rc FOR SELECT * FROM schema.tempobjectlist;
 
    RETURN rc; 
END;
'
LANGUAGE 'plpgsql';
 
 

this program show the error when create the function in PGadmin database
error is
syntax error at or near
line3 columnid int4;

[michaelb]

Hi,
I can't spot anything wrong around line 3. The function qualifier looks odd, but considering that Postgres did not complain about it I have to assume that you indeed created schema with the name "schema". Not a good choice for a schema name, but let's hope this won't cause any problems.

Lets try to get some more info, could you please run these queries and post the results:

select nspname from pg_namespace;
select current_schema();
select version();
 

At the same time you can start debugging the problem;
Get rid of the schema qualifier and start with a truncated version of your function, something like this:

CREATE OR REPLACE FUNCTION _pg_object_getobjectlist(varchar)
RETURNS refcursor AS
'
DECLARE
columnid int4;
search_string varchar;
temp refcursor;
rc refcursor;
BEGIN
search_string := \'%\' || $1 || \'%\';
return temp;
END;
'
LANGUAGE 'plpgsql';
 

To eliminate any possible interference you may want to do it directly in psql session, rather than going through pgAdmin.

Let us know what happened.

[rajeev85]

thanks for suggestion. i completly solve this problem.