Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fA6JbIm91377 for ; Tue, 6 Nov 2001 14:37:19 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id fA6JSI716388 for pgsql-sql@postgresql.org; Tue, 6 Nov 2001 14:28:18 -0500 (EST) (envelope-from news) From: "Alexander Kunz" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: trouble using FOR ... IN EXECUTE query LOOPs in PL/pgSQL Date: Tue, 6 Nov 2001 20:29:46 +0100 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 54 Message-ID: <9s9dki$g01$1@news.tht.net> References: <85256AFC.0060FA3E.00@procyon.terc.edu> X-Complaints-To: scrappy@hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 To: pgsql-sql@postgresql.org X-Archive-Number: 200111/100 X-Sequence-Number: 5484 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 -------------------------------------------------------------------------