Re: trouble using FOR ... IN EXECUTE query LOOPs in PL/pgSQL

Hi Jon,

the field for a loop must be from type record. I have insert some line, but
not testet. ( No time to do this )

> CREATE FUNCTION check_foreign_key ( varchar(32), varchar(32), integer )
> RETURNS text AS '
>      DECLARE
>           table_name ALIAS FOR $1;
>           field_name ALIAS FOR $2;
>           field_value ALIAS FOR $3;
>           key_check_query TEXT;
>           check_count INTEGER;
>           field_value_exists BOOLEAN := ''f'';
------->    rec RECORD;
>
>      BEGIN
>           key_check_query :=
>           (
>           ''SELECT COUNT(*) AS check_count FROM ''
>                || quote_ident(table_name)
>                || '' WHERE ''
>                || quote_ident(field_name)
>                || '' = ''
>                || quote_literal(field_value)
>                || '';''
>           );
>
>           FOR rec IN EXECUTE key_check_query LOOP
-------->       ^^^
>                IF rec.check_count > 0 THEN
-------->           ^^^
>                     field_value_exists := ''t'';
>                END IF;
>                EXIT;
>           END LOOP;
>
>           RETURN field_value_exists;
>      END;
> ' LANGUAGE 'plpgsql';

Regards,
alexander
--
-------------------------------------------------------------------------
Nextra Deutschland       | Alexander Kunz
GmbH & Co. KG            | Capacity Planning & Optimization
Region Mitte             | Tel.:  +49 (0)6151 88008-897
Birkenweg 14a            | Fax:   +49 (0)6151 88008-500
64295 Darmstadt          | Mobil: +49 (0)175 9309601
http://www.nextra.de     | E-Mail: alexander.kunz@nextra.de
-------------------------------------------------------------------------