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
