Re: Getting Out Parameter in the application using libpq

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