Обсуждение: how to call stored procedures with output parameters

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

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