Grokbase › Groups › PostgreSQL › pgsql-novice › March 2011
FAQ
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

Search Discussions

  • Java4dev at Apr 5, 2011 at 6:22 pm ⇧
    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







Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedMar 18, '11 at 12:05p
activeApr 5, '11 at 6:22p
posts2
users1
websitepostgresql.org
irc#postgresql

1 user in discussion

Java4dev: 2 posts

Content

People

Support

Translate

site design / logo © 2023 Grokbase