pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: p.lam@altavista.net
To: pgsql-sql@postgresql.org
Subject: CREATE FUNCTION- Table as argument
Date: Mon, 5 Jun 2000 11:05:57 -0400 (EDT)
Message-ID: <000605110557K5.28624@weba1.iname.net> (raw)

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



Message-ID: <000605110557K5.28624@weba1.iname.net>
Permalink:  ../000605110557K5.28624@weba1.iname.net/
Also on:    postgresql.org/message-id/000605110557K5.28624@weba1.iname.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: p.lam@altavista.net
  Subject: Re: CREATE FUNCTION- Table as argument
  In-Reply-To: <000605110557K5.28624@weba1.iname.net>
  References: <000605110557K5.28624@weba1.iname.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox