I would like to port the following Oracle PL/SQL to PL/pgSQL.
SQL

BEGIN

SELECT MAX(V_SEQ) INTO :#SEQ

FROM TABLENAME WHERE V_NAME = 'WHATEVER';

END;

ENDSQL

Please help.
Matthews Estrice

Not sure I'm going to be much help but anyway..

What is the problem you are experiencing (what doesn't work)?

Can you tell us why the function retrieves the high sequence number this way?
What does it use the result for?

Are you aware ProIV technically does not support output into host variables (eg.SELECT INTO)?

Hi Richard,
SELECT MAX(V_SEQ) INTO :#SEQ

1. First of all SELECT INTO a $ or # scratch variables do work within ORACLE's PL/SQL.Basically,I need same functionally for Postgres(i.e called Porting). The scratch variable is then available globally for usage in the PROIV function.I use this quiet a lot.This eliminates PROIV to read every record and the cycle.
2. In Postgres,I can use CREATE OR REPLACE FUNCTION in rather than PACKAGE method which it is deprecated in Postgres.I thought there would be some differrent ideas of using the PACKAGE method.
3. This can be done at any logic, one does not need to physically map the file into the cycle(optimisation).
4.Instead of packages, I would like to use schemas to organize my Postgres database functions into groups.
5.Example 36.5. Porting a Simple Function from PL/SQL to PL/pgSQL in Posgress 8.1 manual explains it.

Thanks Richard.

The point I was tryimg to make about SELECT INTO is that it is just a happy accident of the driver implementation or something that makes output host variables work with Oracle and that they are officially not supported. The following is a direct quote from the SQL section of the ProIV V5.5 Environment Guide.

"Host variables that pass data from the RDBMS back to ProIV are not supported"

I mentioned it because people do seem to use them and not realise it is unsupported behaviour and because it is very likely the reason for your problem.

Anyway, good luck with finding a workaround.

You can do this with a global function in a db-independent way.
Create a pro-iv file for a db table that contains a 1-character key and a numeric,
and call a global function that executes the SQL on the before read:
SQL
SELECT ' ', MAX(V_SEQ) FROM TABNAME
ENDSQL
and then set and return the value on the read no error.

Mike