>>>>> "DR" == David Richter <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
http://tolikus.hq.aaanet.ru:8080 Phone: (8632)-710071
