Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux