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(dot)kunz(at)nextra(dot)de
-------------------------------------------------------------------------