[SQL] Pg/PLSQL Errors!!

[p . lam]

I am running PostgreSQL 6.5.3 on RedHat Linux 6.1 on a PC.
I am trying to use Pg/PLSQL.

I even tried being conservative enough to copy the example code from
http://www.postgresql.org/docs/user/c40874340.htm as follows:
CREATE FUNCTION add_one (int4) RETURNS int4 AS '
BEGIN
RETURN $1 + 1;
END;
' LANGUAGE 'plpgsql';


though, even that results in "ERROR:  Unrecognized language specified in a CREATE 
FUNCTION: 'pl-pgsql'.  Recognized languages are sql, C, internal and
the created procedural languages."

I have tried variants including PLSQL,PG/PLSQL,PL/SQL,PGSQL  and even 'internal
procedural language(s)' and 'created procedural language(s)' though with the same 
error.

Does anyone know of the name of the postgreSQL's procedural language of form like
CREATE function funcName(arguments) returns returntype AS 'BEGIN
statement block END;' LANGUAGE '??';

Many Thanks!




Get your free email from AltaVista at http://altavista.iname.com

[SQL] Simulating CURSORS??

[p . lam]

I read from messages like 
http://www.postgresql.org/mhonarc/pgsql-sql/1999-11/msg00076.html that CURSORS could 
not be used with pg/plsql, and indeed attempting to do so result in the same kind of 
error highlighted in that message.
Are there any other ways I could with pgplsql simuate the use of Cursors and write 
functions that could fetch and process records row by row retrieved from a SELECT 
statement (and then put the processed data back to another table)?



Get your free email from AltaVista at http://altavista.iname.com

[SQL] CREATE FUNCTION- Table as argument

[p . lam]

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