Re: PL/PGSQL function with parameters

>>>>> "DR" == David Richter <d(dot)richter(at)dkfz(dot)de> 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(at)aaanet(dot)ru
http://tolikus.hq.aaanet.ru:8080 Phone: (8632)-710071