Обсуждение: Best way to insert a row with two columns having the value based on the same argument generated by another postgres function.
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
Regards
Ryan
On 7 July 2016 at 08:56, Silk Parrot <silkparrot@gmail.com> wrote:
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.--
RegardsRyan
A CTE would do that.
Regards,
Sándor
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.
WITH salt_value AS (
SELECT gen_salt('bf', roundsArg) AS value_of_salt
)
INSERT INTO system.enduser
SELECT emailArg, crypt(passwordArg, value_of_salt), value_of_salt, ...
FROM salt_value;
You can probably lose the WITH and just make a subquery FROM...
David J.
Silk Parrot schrieb am 07.07.2016 um 08:56: > 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 roundsArgint) 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. > Something like this maybe? with salted (new_salt) as ( select public.gen_salt('bf', roundsArg) ) INSERT INTO system.enduser (email, password_hash, password_salt, nickname, user_state) select emailArg, public.crypt(passwordArg, s.new_salt), s.new_salt, nicknameArg, 'REGISTERED' from salted RETURNING inserted_uuid;