how to call stored procedures with output parameters
От | java4dev |
---|---|
Тема | how to call stored procedures with output parameters |
Дата | |
Msg-id | 4D834B5D.9030609@gmail.com обсуждение исходный текст |
Ответы |
Re: how to call stored procedures with output parameters
(java4dev <java4dev@gmail.com>)
|
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: