Issue 109041 - Acessing PostgreSQL stored procedures not possible with SQL queries and parameters
Summary: Acessing PostgreSQL stored procedures not possible with SQL queries and param...
Status: CONFIRMED
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOO310m19
Hardware: Mac Mac OS X, all
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-02-08 13:18 UTC by mbalmer
Modified: 2013-01-29 21:47 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments

Note You need to log in before you can comment on or make changes to this issue.
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