From: | Sándor Daku <daku(dot)sandor(at)gmail(dot)com> |
---|---|
To: | Silk Parrot <silkparrot(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: 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 13:12:16 |
Message-ID: | CAKyoTgan4rBM86qjteE8=cc_kUrz9SaGOasMXVtPhwB8Zs9hTA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 7 July 2016 at 08:56, Silk Parrot <silkparrot(at)gmail(dot)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.
>
> --
> Regards
> Ryan
>
A CTE would do that.
https://www.postgresql.org/docs/9.5/static/queries-with.html
Regards,
Sándor
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-07-07 13:14:29 | Re: Best way to insert a row with two columns having the value based on the same argument generated by another postgres function. |
Previous Message | FarjadFarid(ChkNet) | 2016-07-07 11:12:56 | Re: Fastest memmove in C |