By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,326 Members | 2,726 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,326 IT Pros & Developers. It's quick & easy.

how retrive this problem

P: 3
Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE OR REPLACE FUNCTION schema._pg_object_getobjectlist(varchar) 
  3. RETURNS refcursor AS 
  4. '
  5. DECLARE
  6.     columnid int4;
  7.     search_string varchar;
  8.     temp refcursor;
  9.     rc refcursor;
  10. BEGIN
  11.     search_string :=  \'%\' || $1 || \'%\'; 
  12.  
  13.     ----------------------------------------------------------------------------
  14.     -- clean-up temporary object list table before populating it with data
  15.     ----------------------------------------------------------------------------
  16.  
  17.     DELETE FROM schema.tempobjectlist;
  18.  
  19.     OPEN temp FOR SELECT  idobject  FROM  schema.strnodeprops  
  20.     WHERE  spropertyvalue  LIKE search_string; 
  21.  
  22.     LOOP
  23.         FETCH temp INTO columnid;
  24.         EXIT WHEN NOT FOUND;
  25.         PERFORM schema._pg_object_utility(columnid);
  26.     END LOOP;
  27.  
  28.     CLOSE temp;
  29.  
  30.     ----------------------------------------------------------------------------
  31.     -- make up a reference to the resulting table and return it to an external 
  32.     --                              program
  33.     ----------------------------------------------------------------------------
  34.  
  35.     OPEN rc FOR SELECT * FROM schema.tempobjectlist;
  36.  
  37.     RETURN rc; 
  38. END;
  39. '
  40. LANGUAGE 'plpgsql';
  41.  
  42.  
this program show the error when create the function in PGadmin database
error is
syntax error at or near
line3 columnid int4;
Apr 7 '07 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 534
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:
Expand|Select|Wrap|Line Numbers
  1. select nspname from pg_namespace;
  2. select current_schema();
  3. select version();
  4.  
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:
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION _pg_object_getobjectlist(varchar)
  2. RETURNS refcursor AS
  3. '
  4. DECLARE
  5. columnid int4;
  6. search_string varchar;
  7. temp refcursor;
  8. rc refcursor;
  9. BEGIN
  10. search_string := \'%\' || $1 || \'%\';
  11. return temp;
  12. END;
  13. '
  14. LANGUAGE 'plpgsql';
  15.  
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.
Apr 8 '07 #2

P: 3
thanks for suggestion. i completly solve this problem.
Apr 8 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.