Question 264660 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – return true or false existence of postgres tuple

I am trying to implement a code written in mysql to postgres to return true or false existence of tuple, however, I have not been able to do so successfully.

The present code is the one that works without problem in mysql, however, I would like to run it in postgress. How could I implement equivalence

 
CREATE FUNCTION searchUser(inPhone VARCHAR(13)) 
RETURNS bool
BEGIN
 
   RETURN (CASE WHEN EXISTS(
        SELECT 1   
        FROM client   
        WHERE phone=inPhone)
        THEN 1 ELSE 0 END );

 END//

I have tried to do this in postgres, but it doesn’t work, throwing sitaxix error

CREATE FUNCTION searchUser(inPhone VARCHAR(13))  
BEGIN
RETURN CASE WHEN EXISTS
    (SELECT 1 FROM client WHERE  phone=inPhone) 
    THEN '1' 
    ELSE '0';
END;
$$ LANGUAGE plpgsql;
Tags:

3

Answers


  1. 0

    You can also Return BOOLEAN with Postgres:

    CREATE FUNCTION searchUser(inPhone VARCHAR(13)) RETURNS BOOLEAN  
AS $$
BEGIN
RETURN CASE WHEN EXISTS
    (SELECT 1 FROM client WHERE  phone=inPhone) 
    THEN TRUE
    ELSE FALSE
  END;
END;
$$ LANGUAGE plpgsql;

    Result:

                 Table "public.client"
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
 phone  | text |           |          | 

postgres=# select * from client;
 phone 
-------
 foo
(1 row)

postgres=# select * from searchUser('foo');
 searchuser 
------------
 t
(1 row)

postgres=# select * from searchUser('bar');
 searchuser 
------------
 f
(1 row)

    You can even simplify the function further with this:

    postgres=# CREATE FUNCTION searchUser(inPhone VARCHAR(13)) RETURNS BOOLEAN  
AS $$
BEGIN
RETURN EXISTS (SELECT 1 FROM client WHERE  phone=inPhone) ;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# select * from searchUser('foo');
 searchuser 
------------
 t
(1 row)

postgres=# select * from searchUser('bar');
 searchuser 
------------
 f
(1 row)
    Login or Signup to reply.
  2. 0

    here is how you can do it:

    CREATE OR REPLACE FUNCTION searchUser(inPhone VARCHAR(13)) 
RETURNS BOOLEAN AS $$
BEGIN
   RETURN (EXISTS (SELECT 1 FROM client WHERE phone = inPhone));
END;
$$ LANGUAGE plpgsql;

    fiddle demo

    Login or Signup to reply.
  3. 0

    For a simple function like this, you don’t need plpgsql. SQL is good enough, faster and can be optimized by the planner.

    CREATE OR REPLACE FUNCTION searchUser(inPhone VARCHAR(13))
RETURNS bool
LANGUAGE sql
AS
$$
    SELECT count(*) >= 1 -- will always return TRUE or FALSE
    FROM client
    WHERE phone = $1;
$$;

    Or as @Bergi asked, using EXISTS:

    CREATE OR REPLACE FUNCTION searchuser(inphone VARCHAR(13))
    RETURNS BOOL
    LANGUAGE sql
AS
$$
    SELECT EXISTS(SELECT 1 FROM client WHERE phone = $1);
$$;

    And again using the language SQL

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search