0

recently I have created stored procedure in Postgres Sql which deals about with hashing passwords. When used via PgAdmin tool it works like a charm as stated below.

I have also installed extension pgcrypto.

SELECT horeca_user.update_token(
    1, //user_id
    1, //type
    'sdsfsdfs' //token
)

It works when called via pgadmin tool, but not inside laravel app as stated below.

$token=\DB::select("SELECT * from \"horeca_user\".update_token('$type', '$user_id', '$token')");

and finally this is my procedure code

CREATE OR REPLACE FUNCTION horeca_user.update_token(tp integer,userid integer,token character varying)
    RETURNS character varying
    LANGUAGE 'plpgsql' VOLATILE
    PARALLEL UNSAFE
    COST 100
AS $BODY$ 
DECLARE uid INTEGER;
DECLARE tok character varying;
BEGIN
    uid:=0;
    Select count(*) as total from horeca_user.user_tokens where user_id=$2 into uid;

    if uid=0 THEN
       Insert into horeca_user.user_tokens(id, user_id, tip) values($3, $2, $1);
     else
       Update horeca_user.user_tokens set id=$3, tip=$1 where user_id=$2;
     END IF;

     SELECT encode(digest(token, 'sha256'), 'hex') as tt into tok;

     return tok;
END

$BODY$;
  • in PGAdmin you call SELECT horeca_user.update_token() , but in the code SELECT * FROM horeca_user.update_token(). You just need to use the same syntax I guess – a_vlad Mar 31 '19 at 22:41
  • @a_vlad I just checked that query with PDO connection in pure PHP. Query worked as expected. From here I come to decision that problem associated with Laravel. Below is full code. $db = new PDO("pgsql:dbname=Horecami;host=127.0.0.1", "postgres", "1" ); $sql = 'Select horeca_user.update_token(?, ?, ?)'; $stmt = $db->prepare($sql); $type=1; $id=1; $z='az'; $stmt->bindValue(1, $type, PDO::PARAM_INT); $stmt->bindValue(2, $id, PDO::PARAM_INT); $stmt->bindValue(3, $z, PDO::PARAM_STR); $stmt->execute(); $res=$stmt->fetchAll(); – Azer Apr 1 '19 at 6:16
  • ok, is string copy/pasted? what about unnesessary \ in $token=\DB::select ? – a_vlad Apr 1 '19 at 9:31
  • no "\" this means root namespace. It does not matter. – Azer Apr 1 '19 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.