A B wrote: > Here is the entire function and it fails with the names > c2,c2div,c3,c3div, but if names are changed, it works! > (by works I mean I get the "hello" lines printed) There is nothing > wrong with the select statement either, that works fine if I run it > stand-alone, or with the names of c2,c2div,c3,c3div changed. > > CREATE OR REPLACE FUNCTION foo(pid_ INTEGER) RETURNS void AS $$ > DECLARE > c2 REAL; > c2div REAL; > c3 REAL; > c3div REAL; > weights RECORD; > tmp RECORD; > retval RECORD; > t RECORD; > BEGIN > RAISE NOTICE 'starting...'; > FOR tmp IN SELECT id,c2,c3 FROM Master WHERE pid=pid_ AND c3 !=0 LOOP > RAISE NOTICE 'hello %',tmp.id; > END LOOP; > RETURN; > END; $$ LANGUAGE plpgsql;
That is because c2 and c3 in the SELECT statement are replaced with the variables before the SQL statement is executed. See the documentation: http://www.postgresql.org/docs/current/static/plpgsql-implementation.html#PLPGSQL-VAR-SUBST You should qualify the column names: SELECT master.id, master.c2, master.c3 FROM master WHERE ... AND master.c3 <> 0 Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
