Getting Out Parameter in the application using libpq

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 any work 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:

From: Merlin Moncure <mmoncure@gmail.com>
Subject: Re: [GENERAL] Getting Out Parameter in the application using libpq
To: "Ehsan Haq" <ehsan_haq98@yahoo.com>
Cc: pgsql-general@postgresql.org
Date: Friday, September 11, 2009, 12:08 PM

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 any work 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 REPLACE
Function getOutVarchar(outvarchar OUT varchar2) RETURN NUMBER
IS
BEGIN
   outvarchar:='This is Out String';
   RETURN 1;
END getOutVarchar;
However 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:

From: Merlin Moncure <mmoncure@gmail.com>
Subject: Re: [GENERAL] Getting Out Parameter in the application using libpq
To: "Ehsan Haq" <ehsan_haq98@yahoo.com>
Cc: pgsql-general@postgresql.org
Date: Saturday, September 12, 2009, 1:21 PM

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

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