Assigning variables to output parameters in a PostgreSQL trigger

I'm starting a PostgreSQL project, after many years with Sybase and SQL Server, and am new to it's quirks, but so far have been impressed.

I've written a small function that returns OUT parameters. I pass in a clear password and the function returns the encrypted password and a salt. Here it is:

CREATE OR REPLACE FUNCTION iSecGenPwdSalt(pwdin text, out salt text, out userpassword text)
AS 
$BODY$
DECLARE 

    BEGIN
        -- Generate a salt...
        salt = gen_salt('bf', 10);

        -- Now generate the password hash...
        userpassword = crypt(pwdin, salt);
    END;
$BODY$
  LAN开发者_如何学编程GUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION iSecGenPwdSalt(text, out text , out text ) OWNER TO postgres; 

I'd like to call this function within a trigger and use the values to populate the password and salt columns I have, but I can't fathom the syntax. I've tried multiple variations ona theme but I'm still having problems.

select salt, userpassword from iSecGenPwdSalt('mymagicpassword');

Returns it's results beautifully, so I'd like to call this function and get the results into a pair of variables in a trigger so I can assign the values.

Here's my code:

SELECT usersalt=salt, userpwd=userpassword 
  FROM iSecGenPwdSalt(NEW.systemuserpassword);

The error I get is thus:

ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function "isec_trg_systemuser" line 13 at SQL statement

So simply assigning a pair of variables to a resultset derived from OUT parameters in a trigger is causing me grief.

---

In your specific situation I think that you could use SELECT INTO statement with multiple columns:

CREATE OR REPLACE FUNCTION userAuth_insert_f() RETURNS TRIGGER AS $$
BEGIN
    SELECT INTO NEW.usersalt, NEW.userpwd
        salt, userpassword FROM iSecGenPwdSalt(NEW.systemuserpassword);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Full example (using pgcrypto contrib module):

DROP TABLE IF EXISTS userAuth; 
CREATE TABLE userAuth (
    id serial,
    systemuserpassword text,
    usersalt text,
    userpwd text
);

CREATE OR REPLACE FUNCTION iSecGenPwdSalt
    (pwdin text, OUT salt text, OUT userpassword text)
AS $$ BEGIN
    salt = gen_salt('bf', 10);
    userpassword = crypt(pwdin, salt);
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS userAuth_insert_t ON userAuth;
CREATE TRIGGER userAuth_insert_t
    BEFORE INSERT ON userAuth
    FOR EACH ROW
    EXECUTE PROCEDURE userAuth_insert_f();

Invoking:

=>INSERT INTO userAuth (systemuserpassword) VALUES ('mymagicpassword');
INSERT 0 1
=>SELECT usersalt, userpwd FROM userauth;
           usersalt            |                           userpwd                
-------------------------------+---------------------------------------------------
 $2a$10$7FqU5a/DrM.CWGlQPlGz6e | $2a$10$7FqU5a/DrM.CWGlQPlGz6eLjuN3UTwh/hlgxTnogS..
(1 row)

---

This isn't really an answer to the question you're asking, but I had to do a similar thing recently, and I did it with a view and rules instead of triggers. I hope this is helpful despite not really being an answer to the question your asked directly.

Here is my user table and view:

CREATE TABLE users_t (
  username VARCHAR(16) PRIMARY KEY,
  email    VARCHAR UNIQUE,
  password CHAR(60),
  salt     CHAR(29)
);

CREATE VIEW users AS SELECT username, email, '********'::varchar AS password FROM users_t;

The insert rule takes care of generating the salt value:

CREATE RULE users_insert_rule AS ON INSERT TO users DO INSTEAD
INSERT INTO users_t (username, email, password, salt) 
  SELECT new.username, new.email, crypt(new.password, salt.salt), salt.salt 
  FROM (SELECT gen_salt('bf') as salt) salt
RETURNING username, email, '********'::varchar;

For completeness I'm including the update and delete rules, even though they aren't especially relevant:

CREATE RULE users_update_rule AS ON UPDATE TO users DO INSTEAD
  UPDATE users_t 
  SET 
    username = new.username, 
    email = new.email, 
    password = crypt(new.password, salt)
RETURNING username, email, '********'::varchar;

CREATE RULE users_delete_rule AS ON DELETE TO users DO INSTEAD
  DELETE FROM users_t WHERE username = old.username
RETURNING username, email, '********'::varchar;

Then I wrote an authentication function which takes care of using the salt:

CREATE OR REPLACE FUNCTION authenticate(varchar, varchar) RETURNS setof users AS $$
SELECT * FROM users 
WHERE username =
  (SELECT username FROM users_t 
   WHERE username = $1 AND crypt($2, salt) = password)
$$ LANGUAGE sql;

So you can "log in" by querying SELECT * FROM authenticate('username', 'password'); and your users are created by inserting into the users view. You can set the permissions such that you have an unprivileged role that can see the users view but not the underlying users_t table. This way you have an easy to interface with users table with salted passwords without having to deal with the salt in the application.