CREATE FUNCTION- Table as argument

From: p(dot)lam(at)altavista(dot)net
To: pgsql-sql(at)postgresql(dot)org
Subject: CREATE FUNCTION- Table as argument
Date: 2000-06-05 15:05:57
Message-ID: 000605110557K5.28624@weba1.iname.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2000-06-05 15:27:49 Re: Default timestamp value
Previous Message Jean-Marc Libs 2000-06-05 13:48:30 Re: Antw: Problem with joins