Re: pg/plsql question

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
>
>