Skip site navigation (1) Skip section navigation (2)

Re: how to call stored procedures with output parameters

From: java4dev <java4dev(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: how to call stored procedures with output parameters
Date: 2011-04-05 18:26:12
Message-ID: 4D9B5EC4.3080602@gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
This seems to be a very difficult question as I haven't receive any 
answer yet from the list.
I managed though after several trial and error tries to succeed to call 
a stored procedure from java.
It seems that stored procedures behave different in PostgreSQL compared 
to MySQL and Oracle.

One big difference is that if you stored procedure/function (PostgreSQL 
has only functions not procedures) returns something only using the 
keyword RETURNS then when calling the statement you should register the 
out parameter as the first parameter in the callable statement.

If on the other hand you declare in you stored procedure several OUT 
parameters then you may address all the parameters in the correct order 
as they are declared.

Connection connection = null;
CallableStatement callableStatement = null;
ResultSet resultSet = null;
...
callableStatement = connection.prepareCall("{call 
aaa.validate_credential(?, ?, ?, ?)}");
callableStatement.setInt(1, principalId); //p_pk_principal_id_in IN 
aaa.principals.pk_principal_id%TYPE
callableStatement.setString(2, type); // p_credential_type_in IN 
aaa.credentials.credential_type%TYPE
callableStatement.setString(3, password); // p_credential_value_in IN 
aaa.credentials.credential_value%TYPE
callableStatement.registerOutParameter(4, Types.INTEGER); 
//p_return_code_out OUT INTEGER

everything else is as usuall.
Now I am still having problem on how to call some stored procedures from 
the console.
It seems that in console you just forget the OUT parameters
SELECT aaa.validate_credential(1, 'password', 'secret');

and the same goes inside the function you just assign the out parameters 
to variables

SELECT aaa.validate_credential(1, 'password', 'secret') INTO v_return_code;

If there are several out parameters then in the console you must call 
the function like
SELECT * FROM function(...

and in the PL/PgSQL
SELECT aaa.function(1,2) INTO v_1, v_2, v_3 v_4;

My problem is when the function returns several values and especially a 
record. e.g.
CREATE OR REPLACE FUNCTION aaa.authenticate (
     p_name_in IN aaa.principals.principal_name %TYPE,
     p_passwd_in IN aaa.credentials.credential_value%TYPE,
     p_pk_principal_id_out OUT aaa.principals.pk_principal_id%TYPE,
     p_groups_out OUT refcursor,
     p_return_code_out OUT INTEGER)

to call this you should call it like this

SELECT  * FROM aaa.authenticate('myuser', 'secret');

the problem is that I get this

<unnamed portal 4> 0

How do I see the values inside the <unnamed portal 4>??????

Can someone please help me on this.

Nikolas

Στις 18/3/2011 2:09 μμ, ο/η java4dev έγραψε:
> Hello,
>
> I am having problem with PostgreSQl syntax.
> setting values anf how to call stored procedures with output parameteres.
> From what I have seen by googling the subject it seems that PostgreSQL 
> requires that you do not provide the out parameters.
>
> two tables
>
> DROP SCHEMA IF EXISTS aaa CASCADE ;
> CREATE SCHEMA aaa
>   AUTHORIZATION myuser;
>
> CREATE TABLE aaa.principals (
>   pk_principal_id INTEGER NOT NULL,
>   principal_name CHARACTER VARYING(200) NOT NULL,
>   CONSTRAINT IXP_PK_PRINCIPAL_ID PRIMARY KEY (pk_principal_id)
> );
>
> CREATE TABLE aaa.credentials (
>   pk_credential_id INTEGER NOT NULL,
>   fk_principal_id INTEGER NOT NULL,
>   credential_value CHARACTER VARYING(254),
>   credential_type SMALLINT NOT NULL,
>   CONSTRAINT IXP_PK_CREDENTIAL_ID PRIMARY KEY (pk_credential_id),
>   CONSTRAINT FK_AAA_CREDENTIAL_1 FOREIGN KEY (fk_principal_id) 
> REFERENCES aaa.principals (pk_principal_id) ON DELETE CASCADE
> );
>
> a few things I am trying to do and they do not work
>
> CREATE OR REPLACE FUNCTION aaa.unlock_principal(p_pk_principal_id_in 
> IN aaa.principals.pk_principal_id%TYPE)
> RETURNS void
>   AS $BODY$
>   BEGIN
>     UPDATE aaa.principals SET aaa.principals.is_unlocked = TRUE
>       WHERE aaa.principals.pk_principal_id = p_pk_principal_id_in;
>   END;
>   $BODY$ LANGUAGE plpgsql VOLATILE;
>
> ALTER FUNCTION 
> aaa.unlock_principal(aaa.principals.pk_principal_id%TYPE) OWNER TO 
> myuser;
>
> I am calling this function from the console like this
>
> SELECT aaa.unlock_principal(10);
>
> In this function it seems that aaa.principals.is_unlocked  is wrong.
> What are the rules for fully qualifying the name of a field or a table?
>
> I also have the following
>
> CREATE OR REPLACE FUNCTION aaa.validate_credential (
>     p_pk_principal_id_in IN aaa.principals.pk_principal_id%TYPE,
>     p_credential_type_in IN aaa.credentials.credential_type%TYPE,
>     p_credential_value_in IN aaa.credentials.credential_value%TYPE,
>     p_return_code_out OUT INTEGER)
>   AS $BODY$
>   DECLARE
>     v_credential_value aaa.credentials.credential_value%TYPE;
>   BEGIN
>     p_return_code_out := 0; -- C_OPERATION_SUCCESFULL;
>
>     BEGIN
>       SELECT credential_value INTO STRICT v_credential_value FROM 
> aaa.credentials
>         WHERE fk_principal_id = p_pk_principal_id_in AND 
> credential_type = p_credential_type_in;
>     EXCEPTION
>     WHEN TOO_MANY_ROWS THEN
>       p_return_code_out := 3; -- C_TOO_MANY_ROWS; -- too many rows 
> with same id, consistency problem
>       RETURN;
>     WHEN NO_DATA_FOUND THEN
>       p_return_code_out := 42; -- C_CREDENTIAL_DOES_NOT_EXIST;
>       RETURN;
>     END;
>
>     IF v_credential_value <> p_credential_value_in THEN
>       p_return_code_out := 44; -- C_WRONG_CREDENTIAL; -- wrong password
>       RETURN;
>     END IF;
>
>   END;
>  $BODY$ LANGUAGE plpgsql VOLATILE;
>
>
> the questions here are
> how do I call this function from the console?
> e.g. in MySQL you just define a variable @return as the out parameter
> call aaa.validate_credential(10, 0, 'user', @return);
>
> what is the right syntax in PostgreSQL?
>
> how do I call this function form inside another function?
>
> If a function has 3 out parameters ,  e.g
> CREATE OR REPLACE FUNCTION aaa.authenticate (
>     p_name_in IN aaa.principals.principal_name %TYPE,
>     p_passwd_in IN aaa.credentials.credential_value%TYPE,
>     p_pk_principal_id_out OUT aaa.principals.pk_principal_id%TYPE,
>     p_groups_out OUT refcursor,
>     p_return_code_out OUT INTEGER)
>
> how do I call this from the psql console and how do I call this 
> function form inside another function?
>
> thank you
>
> Nikolas
>
>
>
>
>
>
>
>
>

In response to

pgsql-novice by date

Next:From: Mathieu DuboisDate: 2011-04-07 19:52:28
Subject: What does \timing measure?
Previous:From: raghu ramDate: 2011-04-05 17:18:40
Subject: Re: pg_dump

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group