Re: PL/PGSQL function with parameters - Mailing list pgsql-sql

>>>>> "DR" == David Richter <d.richter@dkfz.de> writes:
DR> Folks,DR> I wrote that function, wich doesn't work. I want to hand over the nameDR> of the tables(relation_table,
update_table)and aDR> column(column_to_fill). The intention is, to use the function also withDR> 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 theDR> Docu
ofwww.postgresql.org and Bruce Monjiam's Book) about parameters inDR> PL/PGSQL - functions.DR> I would be no less
gratefulif anybody give detailed suggestions.
 
DR> CREATE FUNCTION patient_study_restructure (text,text,text) RETURNSDR> 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>
boundinteger;DR> i integer := 0;
 
DR> BEGIN DR> FOR psr_rec IN SELECT * FROM relation_table LOOPDR> UPDATE update_table DR> SET column_to_fill =
psr_rec.parentoidDR>WHERE chilioid = psr_rec.childoid;DR> i := i + 1;DR> END LOOP;DR> IF NOT FOUND THEN RETURN 1; DR>
ELSERETURN i;DR> END IF;DR> END;
 
DR> ' LANGUAGE 'plpgsql';
DR> SELECTDR> patient_study_restructure('relpatient_study000','study','patientoid');

DR> Anybody (Jan Wieck?) who can make some sugestions onDR> the above willDR> 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
duringthe life of the server. Instead, the query is prepared each time the   statement is run. The query-string can be
dynamicallycreated 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
tableidentifiers should be passed to   function quote_ident(). Variables containing literal elements of the dynamic
querystring   should be passed to quote_literal(). Both take the appropriate steps to return the input   text enclosed
insingle 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