Issue 109041

Summary: Acessing PostgreSQL stored procedures not possible with SQL queries and parameters
Product: Base Reporter: mbalmer <marc>
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: issues, mechtilde
Version: OOO310m19   
Target Milestone: ---   
Hardware: Mac   
OS: Mac OS X, all   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---

Description mbalmer 2010-02-08 13:18:19 UTC
Our database consists of table, views, and a substantial
number of stored procedures, especially for the more complex reports and
such.

E.g, to get the balance of all accounts, you would send the following
SQL code to the database:

SELECT * FROM acf_balance_all()

To get the balance from only on account, you would use

SELECT * FROM acf_balance('1000')

('1000' is the account number).

When entering the SQL code in the query editor in non-native SQL mode, it
outputs a syntax error, i.e. it seems to be unable to handle the
PostgreSQL stored procedure calls.  One can, however, set the query editor
to native SQL mode, then the SQL code is passed as is to the database
backend and no error is output.  But with native SQL mode, we loose the
possibility to have OpenOffice ask the user for parameters.  We can only
use calls without parameters.  But most stored procedurs actually
require paramaters.

There is a second (and maybe unrelated) problem:  OpenOffice Base seems
for each query to send a generated query to the database server to check
if that table in the query exists.  It does something like

SELECT COUNT(*) FROM <tablename> WHERE 0 = 1

This will of course never return any data, but it will raise an error if
<tablename> does not exist, so I assume this is done to check for the
presence of a table.  But again, this does not work when a query instead
of table specifies a stored procedure in a SELECT.
Comment 1 Mechtilde 2010-02-08 14:14:43 UTC
add to CC
Comment 2 mbalmer 2010-02-08 16:01:58 UTC
Here is a function to test, create a database and install the PL/pgSQL language
in it using the 'creatlang' command line utility.  Then load the following
function (put it in file, and from within psql read it in using \i <filename>):

CREATE OR REPLACE FUNCTION
        ooo_test(INTEGER)
RETURNS
        INTEGER
AS $$
DECLARE
        nr ALIAS FOR $1;
BEGIN
        RETURN nr * 2;
END;
$$ LANGUAGE plpgsql;
Comment 3 Mechtilde 2010-02-14 13:45:17 UTC
there is an intensive detailed discussion at dev@dba.openoffice.org

so this issue is set to new