CODEWITHSUNDEEP
sqlpostgresql
Maks.Burkov
Maks.Burkov

Reputation: 626

Types comparison in PostgreSQL , how to compare bigint and etc ..?

I need function will make some logic after comparing types , but i got an error for example :

ERROR: invalid input syntax for type oid: "bigint"

CREATE OR REPLACE FUNCTION loginValidator(luser LoginUserType) RETURNS text [] AS $$

DECLARE
    errors text []; counter SMALLINT = 0;
n_regex varchar; e_regex varchar; p_regex varchar;

BEGIN

  SELECT nickname_r , email_r, password_r INTO n_regex, e_regex, p_regex FROM regex;

    RAISE NOTICE 'Type %',pg_typeof(luser.user_id); // bigint result


    IF luser.nick_name !~ n_regex THEN counter := counter + 1; errors [counter] := luser.nick_name;
    ELSEIF luser.email !~ e_regex THEN counter := counter + 1; errors [counter] := luser.email;
    ELSEIF luser.u_password !~ p_regex THEN counter := counter + 1; errors [counter] := luser.u_password;
    ELSEIF pg_typeof(luser.user_id) != 'bigint' THEN counter := counter + 1; errors [counter] := luser.user_id;
        -- How to compare here the types ?
    END IF;

    return errors;

 END;
$$ language plpgsql;**strong text**


SELECT loginValidator(row(8765768576,'Maks1988','[email protected]','@PlemiaMaks89987'));

Upvotes: 3

Views: 1974

Answers (1)

S-Man
S-Man

Reputation: 23676

pg_typeof gives results of type regtype which cannot be implicit casted from type text. So you have to do an explicit cast:

SELECT pg_typeof(1::bigint) = 'bigint' -- ERROR: invalid input syntax for type oid: "bigint"

SELECT pg_typeof(1::bigint) = 'bigint'::regtype  -- ok; TRUE

Upvotes: 5

Related Questions