On Tue, 2005-03-15 at 18:18 +0100, Fred Blaise wrote: > While I have accomplished what I needed with the pgedit script given by > John, I am still curious as to why mine is not working... > Here is the latest version: > > /* */ > create or replace function fred_on_all() RETURNS integer AS ' > declare > v_schema varchar; > v_user varchar; > v_t varchar; > begin > v_user := ''user''; > v_schema := ''public''; > FOR v_t in select tablename from pg_catalog.pg_tables where > schemaname = v_schema > LOOP > raise notice ''v_t is %'', t; > END LOOP; > return 1; > end; > ' LANGUAGE 'plpgsql'; > > Please note that all ticks above are single ticks. > > Here is what I do to execute it: > excilan=# \i grant.sql > CREATE FUNCTION > excilan=# select fred_on_all(); > ERROR: missing ".." at end of SQL expression > CONTEXT: compile of PL/pgSQL function "fred_on_all" near line 8
taken from http://www.postgresql.org/docs/7.4/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING <quote> Note: The PL/pgSQL parser presently distinguishes the two kinds of FOR loops (integer or query result) by checking whether the target variable mentioned just after FOR has been declared as a record or row variable. If not, it's presumed to be an integer FOR loop. This can cause rather nonintuitive error messages when the true problem is, say, that one has misspelled the variable name after the FOR. Typically the complaint will be something like missing ".." at end of SQL expression. </quote> try (untested): create or replace function fred_on_all() RETURNS integer AS ' declare v_schema varchar; v_user varchar; v_rec RECORD; begin v_user := ''user''; v_schema := ''public''; FOR v_rec in select tablename from pg_catalog.pg_tables where schemaname = v_schema LOOP raise notice ''v_t is %'', v_REC.tablename; END LOOP; return 1; end; ' LANGUAGE 'plpgsql'; gnari ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]