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

how to call stored procedures with output parameters

From: java4dev <java4dev(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: how to call stored procedures with output parameters
Date: 2011-03-18 12:09:01
Message-ID: 4D834B5D.9030609@gmail.com (view raw or flat)
Thread:
Lists: 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










Responses

pgsql-novice by date

Next:From: raghu ramDate: 2011-03-18 12:11:37
Subject: Re: Postgresql iPhone
Previous:From: Andrew HoytDate: 2011-03-18 11:51:54
Subject: Postgresql iPhone

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