Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f15FHEx16354 for ; Mon, 5 Feb 2001 10:17:14 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0UCTuq12092 for ; Tue, 30 Jan 2001 07:29:56 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f0UCHXS92363 for pgsql-sql@postgresql.org; Tue, 30 Jan 2001 07:17:33 -0500 (EST) (envelope-from news) From: David Richter X-Newsgroups: comp.databases.postgresql.sql Subject: PL/PGSQL function with parameters Date: Tue, 30 Jan 2001 13:14:49 +0100 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 50 Message-ID: <3A76B039.9A59890@DKFZ-heidelberg.de> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: scrappy@hub.org X-Mailer: Mozilla 4.7 [en] (X11; I; IRIX64 6.4 IP27) X-Accept-Language: en To: pgsql-sql@postgresql.org X-Archive-Number: 200102/44 X-Sequence-Number: 1213 Folks, I wrote that function, wich doesn't work. I want to hand over the name of the tables(relation_table, update_table) and a column(column_to_fill). The intention is, to use the function also with other tables(not hard coded). BUT this error appears : psql:restructure.sql:32: ERROR: parser: parse error at or near "$1" I didn't found any solution. I would be grateful , if I could get some more Examples(more than in the Docu of www.postgresql.org and Bruce Monjiam's Book) about parameters in PL/PGSQL - functions. I would be no less grateful if anybody give detailed suggestions. CREATE FUNCTION patient_study_restructure (text,text,text) RETURNS integer AS ' DECLARE relation_table ALIAS FOR $1; update_table ALIAS FOR $2; column_to_fill ALIAS FOR $3; psr_rec record; bound integer; i integer := 0; BEGIN FOR psr_rec IN SELECT * FROM relation_table LOOP UPDATE update_table SET column_to_fill = psr_rec.parentoid WHERE chilioid = psr_rec.childoid; i := i + 1; END LOOP; IF NOT FOUND THEN RETURN 1; ELSE RETURN i; END IF; END; ' LANGUAGE 'plpgsql'; SELECT patient_study_restructure('relpatient_study000','study','patientoid'); Anybody (Jan Wieck?) who can make some sugestions on the above will receive my enthusiastic gratitude. David