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:
Illegaluse 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
helpeither.
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
