Thread: at for over select rows

Hi,

I couldn't find the logical error in my function, so I was wondering if there
could be a bug. The function is created, but when I try to use it I get the
following error message:

NOTICE:  Error occurred while executing PL/pgSQL function apaga_constraint
NOTICE:  line 20 at for over select rows
ERROR:  parser: parse error at or near "$1"

The function code follows:

CREATE OR REPLACE FUNCTION apaga_constraint (varchar)
RETURNS INTEGER AS '
DECLARE
  nome       ALIAS FOR $1;
  registro   RECORD;
  qtd        INTEGER;
  constr_oid INTEGER;
BEGIN

  qtd := 0;

    SELECT oid
    INTO constr_oid
    FROM pg_class
    WHERE relname::TEXT = nome;

    -- Caso nao seja chave primaria
    IF NOT FOUND THEN

      RAISE NOTICE ''Nao eh pk: %'', nome;

      FOR registro IN
        SELECT t.tgrelid, COUNT(t.tgname) AS qtd
        FROM pg_trigger t
        WHERE t.tgconstrname::TEXT = nome
        GROUP BY t.tgrelid
      LOOP
        RAISE NOTICE ''Class: % Triggers: %'', registro.tgrelid, registro.qtd;
      END LOOP;

    -- Caso seja chave primaria
    ELSE
      RAISE NOTICE ''Oid: %'', constr_oid;
    END IF;

  RETURN qtd;

END;
' LANGUAGE 'plpgsql';

SELECT apaga_constraint('fk_teste2_teste');

My apologies for this poor english,
Alvaro

-------------------------------------------------
This mail sent through IMP: http://horde.org/imp/

al_nunes@atua.com.br writes:
> NOTICE:  Error occurred while executing PL/pgSQL function apaga_constraint
> NOTICE:  line 20 at for over select rows
> ERROR:  parser: parse error at or near "$1"

> DECLARE
>   qtd        INTEGER;
    ^^^

>       FOR registro IN
>         SELECT t.tgrelid, COUNT(t.tgname) AS qtd
                                               ^^^

Never use a name in an SQL statement that is the same as a name you've
declared as a plpgsql variable, unless your intent is for the value of
that variable to be substituted at that point in the statement.  Which
is certainly not what you want here.

            regards, tom lane