Обсуждение: Getting Out Parameter in the application using libpq
| Hi, I am looking for a way to get the OUT parameters of a FUNCTION/PROCEDURE in my application (C++) using C libpq library. I can get the result set of an OUT parameter having REFCURSOR data type through an explicit FETCH ALL from "YYYY" but for OUT parameter of type integer/varchar I dont have a clue. Can anyone tell me how it is done or suggest any work around for this? Using the following code I can get the refcursor. CREATE OR REPLACE Function getAddresses ( pName IN varchar2, outCursor refcursor ) RETURN NUMBER IS BEGIN OPEN outCursor FOR SELECT * FROM "dummyTable" WHERE "name"=pName; return 1; END getAddresses; strcat(statement, "SELECT getAddresses('abc', 'outcursor'); FETCH ALL IN outcursor"); res = PQexec(conn, statement); if (PQresultStatus(res) != PGRES_TUPLES_OK) { throw Exception(PQresultErrorMessage(res)); } cout << "Number of Rows: " << PQntuples(res) << " Number of Columns: " << PQnfields(res) << endl; PQclear(res); Thanks Ehsan |
On Fri, Sep 11, 2009 at 12:31 AM, Ehsan Haq <ehsan_haq98@yahoo.com> wrote: > > Hi, > I am looking for a way to get the OUT parameters of a FUNCTION/PROCEDURE in my application (C++) using C libpq library.I can get the result set of an OUT parameter having REFCURSOR data type through an explicit FETCH ALL from "YYYY"but for OUT parameter of type integer/varchar I dont have a clue. Can anyone tell me how it is done or suggest anywork around for this? name your cursor: also, remember that your cursor is only good for duration of transaction. > Using the following code I can get the refcursor. > > CREATE OR REPLACE > Function getAddresses > ( > pName IN varchar2, outCursor refcursor outCursor := 'outcur'; [...] FETCH all FROM outcur; see: http://www.postgresql.org/docs/8.4/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-USING merlin
| Hi, I still don't get. How can I get the varchar OUT parameter in the application? For Example CREATE OR REPLACE Function getOutVarchar(outvarchar OUT varchar2) RETURN NUMBER IS BEGIN outvarchar:='This is Out String'; RETURN 1; END getOutVarchar; iris=> SELECT getOutVarchar('outVar'); getoutvarchar --------------- 1 (1 row) My question is how can I Select "outVar" so that it is available in my application as a resultset. Thanks Ehsan --- On Fri, 9/11/09, Merlin Moncure <mmoncure@gmail.com> wrote:
|
On Fri, Sep 11, 2009 at 10:30 PM, Ehsan Haq <ehsan_haq98@yahoo.com> wrote: > > Hi, > I still don't get. How can I get the varchar OUT parameter in the application? For Example > > CREATE OR REPLACE > Function getOutVarchar(outvarchar OUT varchar2) RETURN NUMBER if, from libpq: res = PQexec(conn, "SELECT * FROM getOutVarchar()"); the result should have a one column, one row result with a field called outvarchar. merlin
| Hi, First of all the below mentioned function can't be called with "SELECT * FROM getOutVarchar()". Since the Function signature does not match. CREATE OR REPLACEHowever calling the above function with "SELECT * FROM getOutVarchar('abc');" does returns a Single column in a single row with a field named 'outvarchar' but the value of the field is "1" which is obvious due to "RETURN 1" and not "This is Out String". Thanks Ehsan --- On Sat, 9/12/09, Merlin Moncure <mmoncure@gmail.com> wrote:
|
Ehsan Haq wrote:
> I still don't get. How can I get the varchar OUT parameter
> in the application? For Example
>
> CREATE OR REPLACE
> Function getOutVarchar(outvarchar OUT varchar2) RETURN NUMBER
> IS
> BEGIN
> outvarchar:='This is Out String';
> RETURN 1;
> END getOutVarchar;
>
> iris=> SELECT getOutVarchar('outVar');
> getoutvarchar
> ---------------
> 1
> (1 row)
>
> My question is how can I Select "outVar" so that it is
> available in my application as a resultset.
Your sample is not valid PostgreSQL, it looks like you just copied
Oracle code.
If I translate it into PostgreSQL, see what I get:
CREATE OR REPLACE
Function getOutVarchar(outvarchar OUT varchar) RETURNS numeric LANGUAGE plpgsql AS
$$BEGIN
outvarchar:='This is Out String';
RETURN 1;
END;$$;
ERROR: function result type must be character varying because of OUT parameters
The problem you encounter is due to an unhappy choice of syntax
in PostgreSQL function definitions.
If you read the manual and the examples therein you will see that
PostgreSQL does not provide what you consider output parameters.
In PostgreSQL, an output parameter is just a different syntax for
specifying a return value.
So saying
CREATE OR REPLACE
Function getOutVarchar(outvarchar OUT varchar)
is in fact the same as saying
CREATE OR REPLACE
Function getOutVarchar() RETURNS varchar
and in both cases you would invoke the function with
SELECT getoutvarchar()
So your original example would declare a function that returns
one value which is varchar and numeric at the same time, which
is impossible.
My advice is to never mix the different syntaxes for function
definition.
Yours,
Laurenz Albe