From: | Silk Parrot <silkparrot(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Best way to insert a row with two columns having the value based on the same argument generated by another postgres function. |
Date: | 2016-07-07 06:56:20 |
Message-ID: | etPan.577dfd19.71262381.2ac@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I am trying to build a user database. The steps for creating a new user are:
1. Use gen_salt to create a salt.
2. Compute the hash based on the salt and password and store both the hash and the salt into a new row.
The pl/pgsql would look like this:
CREATE OR REPLACE FUNCTION system.create_enduser(IN emailArg TEXT, IN passwordArg TEXT, IN nicknameArg TEXT, IN roundsArg int) RETURNS TEXT AS
$$
#print_strict_params on
DECLARE
salt TEXT;
inserted_uuid TEXT;
BEGIN
salt := public.gen_salt('bf', roundsArg);
INSERT INTO system.enduser (email, password_hash, password_salt, nickname, user_state) VALUES (emailArg, public.crypt(passwordArg, salt), salt, nicknameArg, 'REGISTERED') RETURNING uuid into inserted_uuid;
RETURN inserted_uuid;
END
$$
LANGUAGE 'plpgsql' VOLATILE
;
Is there a way to do this in a single SQL statement without using a function? One way I can think of is using trigger, but that still requires another setup.
Appreciate any help.
--
Regards
Ryan
From | Date | Subject | |
---|---|---|---|
Next Message | arnaud gaboury | 2016-07-07 08:01:41 | Re: Broken after upgrade |
Previous Message | Sameer Kumar | 2016-07-07 04:04:17 | Re: Cascade streaming replication + wal_files - Pgsql 9.2 |