Received: from aaanet.ru (aaanet.ru [213.59.98.254]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f177qZx19918 for ; Wed, 7 Feb 2001 02:52:36 -0500 (EST) (envelope-from tolik@tolikus.hq.aaanet.ru) Received: from tolikus.hq.aaanet.ru ([213.59.98.104]) by aaanet.ru with esmtp (Exim 3.12 #1) id 14QPOy-0009yk-00; Wed, 07 Feb 2001 10:52:32 +0300 Received: (from tolik@localhost) by tolikus.hq.aaanet.ru (8.9.3/8.9.3) id KAA38555; Wed, 7 Feb 2001 10:52:33 +0300 (MSK) (envelope-from tolik) To: David Richter Cc: pgsql-sql@postgresql.org Subject: Re: PL/PGSQL function with parameters References: <3A76B039.9A59890@DKFZ-heidelberg.de> Mime-Version: 1.0 (generated by tm-edit 1.5) Content-Type: text/plain; charset=US-ASCII From: tolik@aaanet.ru (Anatoly K. Lasareff) Date: 07 Feb 2001 10:52:32 +0300 In-Reply-To: David Richter's message of "Tue, 30 Jan 2001 13:14:49 +0100" Message-ID: <86snlrudz3.fsf@tolikus.hq.aaanet.ru> Lines: 87 X-Mailer: Gnus v5.6.45/XEmacs 21.1 - "Canyonlands" X-Archive-Number: 200102/110 X-Sequence-Number: 1288 >>>>> "DR" == David Richter writes: DR> Folks, DR> I wrote that function, wich doesn't work. I want to hand over the name DR> of the tables(relation_table, update_table) and a DR> column(column_to_fill). The intention is, to use the function also with DR> other tables(not hard coded). DR> BUT this error appears : DR> psql:restructure.sql:32: ERROR: parser: parse error at or near "$1" DR> I didn't found any solution. DR> I would be grateful , if I could get some more Examples(more than in the DR> Docu of www.postgresql.org and Bruce Monjiam's Book) about parameters in DR> PL/PGSQL - functions. DR> I would be no less grateful if anybody give detailed suggestions. DR> CREATE FUNCTION patient_study_restructure (text,text,text) RETURNS DR> integer AS ' DR> DECLARE DR> relation_table ALIAS FOR $1; DR> update_table ALIAS FOR $2; DR> column_to_fill ALIAS FOR $3; DR> psr_rec record; DR> bound integer; DR> i integer := 0; DR> BEGIN DR> FOR psr_rec IN SELECT * FROM relation_table LOOP DR> UPDATE update_table DR> SET column_to_fill = psr_rec.parentoid DR> WHERE chilioid = psr_rec.childoid; DR> i := i + 1; DR> END LOOP; DR> IF NOT FOUND THEN RETURN 1; DR> ELSE RETURN i; DR> END IF; DR> END; DR> ' LANGUAGE 'plpgsql'; DR> SELECT DR> patient_study_restructure('relpatient_study000','study','patientoid'); DR> Anybody (Jan Wieck?) who can make some sugestions on DR> the above will DR> receive my enthusiastic gratitude. DR> David You _cannot_ use parameters value as table or column name inside plpgsql function. So your construct SELECT * FROM relation_table (and others similar) is wrong. The same in other words: you cannot make dynamic queries by plpgsql. BUT! You can use EXECUTE statement which exists in 7.1. Here is some doc: EXECUTE {query-string} where query-string is a string of type TEXT containing the query to be executed. Unlike all other queries in PL/pgSQL, a query run by an EXECUTE statement is not prepared and saved just once during the life of the server. Instead, the query is prepared each time the statement is run. The query-string can be dynamically created within the procedure to perform actions on variable tables and fields. The results from SELECT queries are discarded by EXECUTE unless SELECT INTO is used to save the results into a table. An example: EXECUTE ''UPDATE tbl SET '' || quote_ident(fieldname) || '' = '' || quote_literal(newvalue) || '' WHERE ...''; This example shows use of the functions quote_ident(TEXT) and quote_literal(TEXT). Variables containing field and table identifiers should be passed to function quote_ident(). Variables containing literal elements of the dynamic query string should be passed to quote_literal(). Both take the appropriate steps to return the input text enclosed in single or double quotes and with any embedded special characters intact. -- Anatoly K. Lasareff Email: tolik@aaanet.ru http://tolikus.hq.aaanet.ru:8080 Phone: (8632)-710071