On Thu, Jul 7, 2016 at 2:56 AM, Silk Parrot <silkpar...@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. > 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.