that worked :) thanks for your input fred
On Tue, 2005-03-15 at 18:00 +0000, Ragnar Hafstaš wrote: > 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 > >
signature.asc
Description: This is a digitally signed message part