Re: Best way to insert a row with two columns having the value based on the same argument generated by another postgres function.

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;