Received: from rmx308-mta.mail.com (rmx308-mta.mail.com [165.251.48.43]) by hub.org (8.10.1/8.10.1) with ESMTP id e55F63O76713 for ; Mon, 5 Jun 2000 11:06:03 -0400 (EDT) Received: from weba1.iname.net (weba1.iname.net [165.251.4.11]) by rmx308-mta.mail.com (8.9.3/8.9.3) with ESMTP id LAA17245 for ; Mon, 5 Jun 2000 11:05:58 -0400 (EDT) From: p.lam@altavista.net Received: (from root@localhost) by weba1.iname.net (8.9.1a/8.9.2.Alpha2) id LAA09130; Mon, 5 Jun 2000 11:05:57 -0400 (EDT) MIME-Version: 1.0 Message-Id: <000605110557K5.28624@weba1.iname.net> Date: Mon, 5 Jun 2000 11:05:57 -0400 (EDT) Content-Type: Text/Plain Content-Transfer-Encoding: 7bit To: pgsql-sql@postgresql.org Subject: CREATE FUNCTION- Table as argument X-Archive-Number: 200006/37 I am running PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66. I have currently a table like the following: TABA: a|start|finish -+-----+------ R| 4| 8 S| 6| 10 I want the output a table with start incremented by 1, and fininsh as the maximum of fininish in all records. Quite sensibliy, my attemp to use a SQL statement like 'SELECT a,start+1,max(finish) from taba' failed with "ERROR: Illegal use of aggregates or non-group column in target list". Hence, I tried to create a function that would return the maximum fininsh attribute in a table. Hence, CREATE function findMax() RETURNS int4 AS 'SELECT max(finish) from taba;' LANGUAGE 'sql' and then SELECT a, start+1,findMax() from taba; does work, but the problem is, I need a generic function that would find the maximum finish attribute not just for a unique table. Hence I tried the following: CREATE function findMax(varchar) RETURNS int4 AS 'SELECT max(finish) from $1;' LANGUAGE 'sql' However, this results in "ERROR: parser: parse error at or near "$1""; I have tried to substitute varchar with TEXT and NAME, but still the same error persists. Changing $1 to \$1 does not help either. I have even tried alias using: CREATE FUNCTION findMax(varchar) RETURNS int4 AS 'DECLARE tabName ALIAS FOR $1; BEGIN SELECT max(finish) from tabName; END; ' LANGUAGE 'sql'; this results in "ERROR: parser: parse error at or near "alias"" Does anyone know how I could take in a table name as argument to a SQL function? ---------------------------------------------------------------- Get your free email from AltaVista at http://altavista.iname.com